Using DBMS_LOB to Manipulate LOBs

The DBMS_LOB package can be used to manipulate LOBs from PL/SQL.

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() -- test to see if the file exists on the server

• FILEGETNAME() -- get the directory alias and file name

• FILEISOPEN() -- test to see if the file was opened using the input BFILE locators

• FILEOPEN() -- open a file

The following example calls the TRIM procedure to trim a CLOB value to a smaller length is shown below. This example assumes that the type lob_type has two attributes (id of type INTEGER and data of type CLOB) and that a table (lob_table) of this type (lob_type) has been created.

PROCEDURE Trim_Clob IS
        clob_loc  CLOB;
BEGIN
 -- get the LOB Locator
       SELECT data into clob_loc  FROM lob_table
       WHERE id  =  179 FOR UPDATE;
   -- call the TRIM Routine
       DBMS_LOB.TRIM(clob_loc, 834004);
       COMMIT;
END;

Because this example deals with CLOB data, the second argument (834004) to DBMS_LOB.TRIM specifies the number of characters. If the example dealt with BLOB data, this argument would be interpreted as the number of bytes.