insert, update CLOB

Here is another good example:

You can initialize the LOBs by using the following SQL INSERT statement:

INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), NULL,
    EMPTY_CLOB(), NULL);

External LOBs:

INSERT INTO lob_table VALUES
         (21, NULL, NULL, NULL, BFILENAME('IMG', 'image1.gif'));
    INSERT INTO lob_table VALUES
         (22, NULL, NULL, NULL, BFILENAME('IMG', 'image2.gif'));


UPDATE lob_table SET f_lob = BFILENAME('IMG', 'image3.gif')
        WHERE key_value = 22;

BFILENAME() is a built-in function that is used to initialize the BFILE column to point to the external file.


Selecting a LOB:

DECLARE    image1       BLOB;    image_no     INTEGER := 101;BEGIN
    SELECT b_lob INTO image1 FROM lob_table        WHERE key_value = image_no;
    DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
        DBMS_LOB.GETLENGTH(image1));    -- more LOB routinesEND;


Example of a Read Consistent Locator

INSERT INTO lob_table
   VALUES (1, NULL, 'abcd', NULL, NULL);

COMMIT;

DECLARE
  num_var           INTEGER;
  clob_selected     CLOB;
  clob_updated      CLOB;
  clob_copied       CLOB;
  read_amount       INTEGER;
  read_offset       INTEGER;
  write_amount      INTEGER;
  write_offset      INTEGER;
  buffer            VARCHAR2(20);
 
BEGIN
  -- At time t1:
  SELECT c_lob INTO clob_selected
     FROM lob_table
     WHERE key_value = 1;

  -- At time t2:
  SELECT c_lob INTO clob_updated
     FROM lob_table
     WHERE key_value = 1
     FOR UPDATE;
 
  -- At time t3:
  clob_copied := clob_selected;
  -- After the assignment, both the clob_copied and the
  -- clob_selected have the same snapshot as of the point in time
  -- of the SELECT into clob_selected

  -- Reading from the clob_selected and the clob_copied will  
  -- return the same LOB value. clob_updated also sees the same    
  -- LOB value as of its select:
  read_amount := 10;
  read_offset := 1;  
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'
 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'
 
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcd'
 
  -- At time t4:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset,
       buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcdefg'
 
  -- At time t5:
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'
 
  -- At time t6:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'
END;
/

Example of an Updated LOB Locator
INSERT INTO lob_table
   VALUES (1, NULL, 'abcd', NULL, NULL);

COMMIT;
 
DECLARE
  num_var          INTEGER;
  clob_updated     CLOB;
  clob_copied      CLOB;
  read_amount      INTEGER; ;
  read_offset      INTEGER;
  write_amount     INTEGER;
  write_offset     INTEGER;
  buffer           VARCHAR2(20);
BEGIN
 
-- At time t1:
  SELECT c_lob INTO clob_updated FROM lob_table
      WHERE key_value = 1
      FOR UPDATE;
 
  -- At time t2:
  clob_copied := clob_updated;
  -- after the assign, clob_copied and clob_updated see the same
  -- LOB value
 
  read_amount := 10;
  read_offset := 1;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcd'
 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'
 

  -- At time t3:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset,
        buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcdefg'
 

  -- At time t4:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'
 

  -- At time t5:
  clob_copied := clob_updated;
 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcdefg'
END;
/

Copying internal LOBs

INSERT INTO lob_table1 (key_value, b_lob)
    (SELECT key_value, b_lob FROM lob_table2 T2
     WHERE T2.key_value = 101);

creates a new LOB locator in the table lob_table1, and copies the LOB data from lob_table2 to the location pointed to by a new LOB locator which is inserted into table lob_table1.


----------

LOB Reference

EMPTY_BLOB() and EMPTY_CLOB() Functions

INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), 'abcde', NULL, NULL);
UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001;
INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL, NULL);

BFILENAME() Function

DBMS_LOB Package
The DBMS_LOB package provides routines to access BLOBs, CLOBs, NCLOBs, and BFILEs. You can use DBMS_LOB for access and manipulation of specific parts of a LOB, as well as complete LOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations on BFILEs.

All DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external filesystem.


The routines that can modify BLOB, CLOB, and NCLOB values are:

APPEND() - append the contents of the source LOB to the destination LOB
COPY() - copy all or part of the source LOB to the destination LOB
ERASE() - erase all or part of a LOB
LOADFROMFILE() - load BFILE data into an internal LOB
TRIM() - trim the LOB value to the specified shorter length
WRITE()- write data to the LOB from a specified offset
The routines that read or examine LOB values are:

GETLENGTH() - get the length of the LOB value
INSTR() - return the matching position of the nth occurrence of the pattern in the LOB
READ() - read data from the LOB starting at the specified offset
SUBSTR() - return part of the LOB value starting at the specified offset
The read-only routines specific to BFILEs are:

FILECLOSE() - close the file
FILECLOSEALL()- close all previously opened files
FILEEXISTS() - check if the file exists on the server
FILEGETNAME() - get the directory alias and file name
FILEISOPEN() - check if the file was opened using the input BFILE
locators
FILEOPEN() - open a file