LOB DATA Type DML operation in Oracle

DBMS LOB is used to read, manipulate and modify the BLOB, CLOB and NCLOB datatypes data. Some LOB is stored inside the database like BLOB, CLOB, NCLOB is called internal LOBs and some stored outside the database BFILE is called external LOBs.

Note:
Each LOB has a locator, which can be viewed as a handle or pointer to the actual location.
Selecting a LOB returns the LOB locator instead of the LOB value.
Each LOB has locator, which can be pointer to the actual location.
BFILE is only to enable read-only byte stream I/O access to large files existing on the filesystem.

Create a LOB table

CREATE TABLE lob_table (
id INTEGER,
b_lob BLOB,
c_lob CLOB,
nc_lob NCLOB);


Enter Null value into LOB object or empty function
EMPTY_BLOG or EMPTY_CLOB use both act as NULL value.

INSERT INTO LOB_TABLE VALUES (1001, '1010', 'ABCDE', NULL);
INSERT INTO LOB_TABLE VALUES (1002, EMPTY_BLOB(), 'ABCDE', NULL);
UPDATE LOB_TABLE SET C_LOB = EMPTY_CLOB() WHERE id = 1001;
INSERT INTO LOB_TABLE VALUES (1003, NULL, NULL, NULL);


Check the present data in LOB table

col c_lob for a10
col cn_lob for a10
col b_lob for a10
select * from lob_table;

        
        ID B_LOB      C_LOB      NC_LOB
---------- ---------- ---------- ----------
      1001 1010
      1002            ABCDE
      1003

Example of Read with DBMS_LOB package




set serveroutput on
DECLARE 
  num_var           INTEGER; 
  clob_selected     CLOB; 
  read_amount       INTEGER; 
  read_offset       INTEGER; 
  write_amount      INTEGER; 
  write_offset      INTEGER; 
  buffer            VARCHAR2(20); 
BEGIN
  
  SELECT c_lob INTO clob_selected FROM lob_table WHERE ID = 1002;
  read_amount := 10; 
  read_offset := 1;  
  dbms_lob.read(clob_selected, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
end;
/  
 Example of write with DBMS_LOB package 

SET SERVEROUTPUT ON
DECLARE 
  num_var           INTEGER; 
  clob_selected     CLOB; 
  read_amount       INTEGER; 
  read_offset       INTEGER; 
  write_amount      INTEGER; 
  write_offset      INTEGER; 
  buffer            VARCHAR2(20); 
BEGIN
  SELECT c_lob INTO clob_selected FROM lob_table WHERE ID = 1002;
  -- Write with dbms lob package into clob data type
  write_amount := 8; 
  write_offset := 5; 
  buffer := 'efghijkl'; 
  dbms_lob.write(clob_selected, write_amount, write_offset, buffer);
  read_amount := 10; 
  read_offset := 1;   
  -- Read with dbms lob read package the clob data type 
  dbms_lob.read(clob_selected, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer);  
end;
/  
 Locator for LOB object 
-- Image1 is act as the locator of b_lob

 
set serveroutput on
DECLARE
       image1  BLOB;
       image_no  INTEGER := 1001;
BEGIN
       SELECT b_lob  INTO image1 FROM lob_table WHERE id = image_no;
       dbms_output.put_line(image1);
End;
/

Getlength of lob object

BEGIN
DBMS_OUTPUT.PUT_LINE('Size of the Image is: ',
DBMS_LOB.GETLENGTH(image1));
END;


Delete the LOB data

Delete from lob_Table;
Truncate table lob_table;


For External or Binary File Datatype follow the link: External LOB BFILE Data types READ, DELETE, INSERT operations in Oracle