PL/SQL API for LOBs

8.2 PL/SQL API for LOBs

The DBMS_LOB package enables you to access and make changes to LOBs in PL/SQL.

See Also:

DBMS_LOB for more information on DBMS_LOB package.

Guidelines for Offset and Amount Parameters in DBMS_LOB Operations

The following guidelines apply to the offset and amount parameters used in the DBMS_LOB PL/SQL package procedures:

For character data in all formats, either in fixed-width or variable-width, the amount and offset parameters are in characters. This applies to operations on CLOB and NCLOB data types.
For binary data, the offset and amount parameters are in bytes. This applies to operations on BLOB data types.
When using the DBMS_LOB.READ procedure, the amount parameter should be less than or equal to the size of the buffer, which is limited to 32K. However, the amount parameter can be larger than the size of the LOB data.

Table 8-2 DBMS_LOB functions and procedures for LOBs

Category	Function/Procedure	Description
Sanity Checking	`ISSECUREFILE`	Find out if the `BLOB` or `CLOB` locator is a SecureFile
Open/Close	`OPEN`	Open a LOB
	`ISOPEN`	Check if a LOB is open
	`CLOSE`	Close the LOB
Read Operations	`GETLENGTH`
	`GET_STORAGE_LIMIT`
	`GETCHUNKSIZE`
	`READ`
	`SUBSTR`
	`INSTR`
Modify Operations	`WRITE`	Write data to the LOB at a specified offset
	`WRITEAPPEND`	Write data to the end of the LOB
	`ERASE`	Erase part of a LOB, starting at a specified offset
	`TRIM`	Trim the LOB value to the specified shorter length
Operations involving multiple locators	`COMPARE`	Compare all or part of the value of two LOBs
	`APPEND`	Append a LOB value to another LOB
	`COPY`	Copy all or part of a LOB to another LOB
	`dst` := `src`	Assign LOB locator `src` to LOB locator `dst`
	`CONVERTTOBLOB, CONVERTTOCLOB`	Converts a `BLOB` to a `CLOB` or a `CLOB` to a `BLOB`
	`LOADCLOBFROMFILE,LOADBLOBFROMFILE`	Load `BFILE` data into a LOB
Operations specific to SecureFiles	`GETOPTIONS`	Returns options (deduplication, compression, encryption) for SecureFiles.
	`SETOPTIONS`	Sets LOB features (deduplication and compression) for SecureFiles
	`GETCONTENTTYPE`	Gets the content string for a SecureFiles.
	`SETCONTENTTYPE`	Sets the content string for a SecureFiles.
	`FRAGMENT_DELETE`	Delete the data from the LOB at the given offset for the given length
	`FRAGMENT_INSERT`	Insert the given data (< 32KBytes) into the LOB at the given offset
	`FRAGMENT_MOVE`	Move the given amount of bytes from the given offset to the new given offset
	`FRAGMENT_REPLACE`	Replace the data at the given offset with the given data (< 32kBytes)

Example 8-1 PL/SQL API for LOBs

DECLARE
   retval    INTEGER;
   clob1     CLOB;
   clob2     CLOB;
   clob3     CLOB;
   blob1     BLOB;
   buf       VARCHAR2(32767);
   buflen    INTEGER := 32760;
   loblen1   INTEGER;
     
   -- Following are the variables that you need for the convertToBlob and convertToClob functions
   amt     NUMBER := 0;
   src     NUMBER := 1 ;
   dst     NUMBER := 1 ;
   lang    NUMBER := 0;
   warn    NUMBER;
 
 BEGIN
   SELECT ad_sourcetext INTO clob1 FROM print_media
     WHERE product_id = 1 AND ad_id = 1;

    -- the select statement is defined with FOR UPDATE so that we can write to it
   SELECT ad_finaltext INTO clob2 FROM print_media
     WHERE product_id = 1 AND ad_id =1 FOR UPDATE; 
   /* Note that all the writes to clob2 will get reflected in the column */
 
   /*------------------------------------------------------------------*/
   /*---------------------- Sanity Checking ---------------------------*/
   /*------------------------------------------------------------------*/
   if DBMS_LOB.ISSECUREFILE(clob1) = TRUE then
     DBMS_OUTPUT.PUT_LINE('CLOB1 is SECUREFILE');
   else
     DBMS_OUTPUT.PUT_LINE('CLOB1 is BASICFILE');
   end if;
 
   /*------------------------------------------------------------------*/
   /*----------------------- Open -------------------------------------*/
   /*------------------------------------------------------------------*/
   /* Open clob1 for READs and clob2 for WRITES */
   DBMS_LOB.OPEN(clob1, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.OPEN(clob2, DBMS_LOB.LOB_READWRITE);
 
   /*------------------------------------------------------------------*/
   /*-------------------- Reading from a LOB --------------------------*/
   /*------------------------------------------------------------------*/
   DBMS_OUTPUT.PUT_LINE('storage limit : ' || dbms_lob.get_storage_limit(clob1));
   DBMS_OUTPUT.PUT_LINE('chunk size : ' || dbms_lob.getchunksize(clob1));
 
   loblen1 := DBMS_LOB.GETLENGTH(clob1);
   DBMS_OUTPUT.PUT_LINE('length : ' || loblen1);
 
   DBMS_LOB.READ(clob1, buflen, 1, buf);
   DBMS_OUTPUT.PUT_LINE('read : LOB data : ' || buf);
   DBMS_OUTPUT.PUT_LINE('New buflen : ' || buflen);
 
   DBMS_OUTPUT.PUT_LINE('substr : ' || dbms_lob.substr(clob1, 30, 1));
   DBMS_OUTPUT.PUT_LINE('instr : ' ||
                        DBMS_LOB.INSTR(clob1, 'review of the document', 1, 3));
 
   /*------------------------------------------------------------------*/
   /*-------------------- Modifying a LOB -----------------------------*/
   /*------------------------------------------------------------------*/
   DBMS_LOB.WRITE(clob2, buflen, 10, buf);
   DBMS_LOB.WRITEAPPEND(clob2, buflen, buf);
   buflen := 10;
   DBMS_LOB.ERASE(clob2, buflen, 10);
   DBMS_LOB.TRIM(clob2, 50);
 
   /* Print the LOB just modified */
   buflen := 32760;
   DBMS_LOB.READ(clob2, buflen, 1, buf);
   DBMS_OUTPUT.PUT_LINE('read : LOB data : ' || buf);
   DBMS_OUTPUT.PUT_LINE('New buflen : ' || buflen);
 
   /* Error because clob1 is open in READ mode */
   -- DBMS_LOB.WRITE(clob1, buflen, 10, buf);
 
   /*------------------------------------------------------------------*/
   /*------------- Operations involving 2 locators ---------------------*/
   /*------------------------------------------------------------------*/
 
   retval := DBMS_LOB.COMPARE(clob1, clob2, 100, 1, 1);
   if (retval < 0) then
     DBMS_OUTPUT.PUT_LINE('clob1 is smaller');
   elsif (retval = 0) then
     DBMS_OUTPUT.PUT_LINE('both clobs are equal');
   else
     DBMS_OUTPUT.PUT_LINE('clob1 is larger');
   end if;
 
   DBMS_OUTPUT.PUT_LINE('length before append: ' || DBMS_LOB.GETLENGTH(clob2));
   DBMS_LOB.APPEND(clob2, clob1);
   DBMS_OUTPUT.PUT_LINE('length after append: ' || DBMS_LOB.GETLENGTH(clob2));
 
   DBMS_OUTPUT.PUT_LINE('----------- LOB COPY operation --------');
   DBMS_LOB.COPY(clob2, clob1, loblen1, 100, 1);
   DBMS_OUTPUT.PUT_LINE('length after copy: ' || DBMS_LOB.GETLENGTH(clob2));
 
   /*------------------------------------------------------------------*/
   /*------------------- Convert CLOB to a BLOB -----------------------*/
   /*------------------------------------------------------------------*/
   DBMS_LOB.CREATETEMPORARY( blob1, false );
   dst := 1;
   src := 1;
   amt := 5;
   DBMS_LOB.CONVERTTOBLOB(blob1, clob2, amt, dst, src, DBMS_LOB.DEFAULT_CSID,
                          lang, warn);
   DBMS_OUTPUT.PUT_LINE(' Source offset returned      ' || src ) ;
   DBMS_OUTPUT.PUT_LINE(' Destination offset returned ' || dst ) ;
   DBMS_OUTPUT.PUT_LINE(' Length of CLOB      ' || dbms_lob.getlength(clob2) ) ;
   DBMS_OUTPUT.PUT_LINE(' Length of BLOB      ' || dbms_lob.getlength(blob1) ) ;
   DBMS_OUTPUT.PUT_LINE(' Warning returned    ' || warn);
   DBMS_OUTPUT.PUT_LINE(' OUTPUT BLOB contents = ' || rawtohex(blob1));
 
   /*------------------------------------------------------------------*/
   /*-------------------- Convert BLOB to a CLOB ----------------------*/
   /*------------------------------------------------------------------*/
   DBMS_LOB.CREATETEMPORARY( clob3, false );
   dst := 1;
   src := 1;
   amt := 4;
   DBMS_LOB.CONVERTTOCLOB(clob3, blob1, amt, dst, src, DBMS_LOB.DEFAULT_CSID,
                          lang, warn);
   DBMS_OUTPUT.PUT_LINE(' Source offset returned      ' || src ) ;
   DBMS_OUTPUT.PUT_LINE(' Destination offset returned ' || dst ) ;
   DBMS_OUTPUT.PUT_LINE(' Length of BLOB      ' || DBMS_LOB.GETLENGTH(blob1) ) ;
   DBMS_OUTPUT.PUT_LINE(' Length of CLOB      ' || DBMS_LOB.GETLENGTH(clob3) ) ;
   DBMS_OUTPUT.PUT_LINE(' Warning returned    ' || warn);
   DBMS_OUTPUT.PUT_LINE(' INPUT BLOB contents =  ' || rawtohex(blob1));
   DBMS_OUTPUT.PUT_LINE(' OUTPUT CLOB contents = ' || clob3);
 
   /*------------------------------------------------------------------*/
   /*----------------------- Close ------------------------------------*/
   /*------------------------------------------------------------------*/
   DBMS_OUTPUT.PUT_LINE('------------- CLOSE ---------------');
   DBMS_LOB.CLOSE(clob2);
 
   if (DBMS_LOB.ISOPEN(clob1) = 1) then
     DBMS_LOB.CLOSE(clob1);
   END if;
 
   COMMIT;
END;
/

Example 8-2 PL/SQL APIs for SecureFile specific operations

conn pm/pm
 
-- alter the table to make lob storage as securefile
-- assume tablespace tbs_1 is ASSM
alter table print_media move
lob(ad_composite)  store as securefile (deduplicate compress tablespace tbs_1)
lob(ad_sourcetext) store as securefile (compress tablespace tbs_1)
lob(ad_finaltext)  store as securefile (compress tablespace tbs_1)
lob(ad_photo)      store as securefile (tablespace tbs_1);
 
SET SERVEROUTPUT ON
 
DECLARE
 clob1             CLOB;
 blob1             BLOB;
 result            BINARY_INTEGER;
 
 /* --- variables for setcontenttype, getcontenttype ----*/
 get_media_type    VARCHAR2(128);
 set_media_type    VARCHAR2(128);
 
 /* --- variables for delta operations --------*/
 amount            INTEGER;
 offset            INTEGER;
 buffer            VARCHAR2(30);
 readbuf           VARCHAR2(50);
 read_amt          INTEGER;
 src_offset        INTEGER;
 dest_offset       INTEGER;
 amount_old        INTEGER;
BEGIN
 -- fetch clob, blob values
 SELECT ad_sourcetext, ad_composite
 INTO   clob1, blob1
 FROM   print_media
 WHERE  product_id = 2056 FOR UPDATE;
  
 /*------------------------------------------------------------------*/
 /*---------------------- Get Options -------------------------------*/
 /*------------------------------------------------------------------*/
 -- check whether compress option is enabled
 result := DBMS_LOB.GETOPTIONS(clob1, DBMS_LOB.OPT_COMPRESS);
 DBMS_OUTPUT.PUT_LINE('Get compress option on ad_sourcetext: '||result);
  
 -- check whether compress + deduplicate is enabled
 result := DBMS_LOB.GETOPTIONS(blob1, DBMS_LOB.OPT_DEDUPLICATE +
                                      DBMS_LOB.OPT_COMPRESS);
 DBMS_OUTPUT.PUT_LINE('Get compress + deduplicate option on ad_composite: '||result);
 
 /*------------------------------------------------------------------*/
 /*---------------------- Set Options -------------------------------*/
 /*------------------------------------------------------------------*/
 -- turn off compression 
 DBMS_LOB.SETOPTIONS(clob1, DBMS_LOB.OPT_COMPRESS, DBMS_LOB.COMPRESS_OFF);
 -- getoptions should be 0 now
 result := DBMS_LOB.GETOPTIONS(clob1, DBMS_LOB.OPT_COMPRESS);
 DBMS_OUTPUT.PUT_LINE('Compress option on clob1: '||result);
 
 -- turn off deduplication
 DBMS_LOB.SETOPTIONS(blob1, DBMS_LOB.OPT_DEDUPLICATE, DBMS_LOB.DEDUPLICATE_OFF);
 -- getoptions should be 0 now
 result := DBMS_LOB.GETOPTIONS(blob1, DBMS_LOB.OPT_DEDUPLICATE);
 DBMS_OUTPUT.PUT_LINE('Deduplicate option on blob1: '||result);
 
 /*------------------------------------------------------------------*/
 /*----------- Getcontenttype, Setcontenttype -----------------------*/
 /*------------------------------------------------------------------*/
 -- get contenttype -- should be null as content type is not set yet
 DBMS_OUTPUT.PUT_LINE(CHR(10)||'clob1 contenttype: ' || dbms_lob.getcontenttype(clob1));
 
 set_media_type := 'text/plain';
 DBMS_LOB.SETCONTENTTYPE(clob1, set_media_type);
 
 DBMS_OUTPUT.PUT_LINE('Clob1 contenttype: ' || dbms_lob.getcontenttype(clob1));
 
 -- setcontenttype for blob
 DBMS_OUTPUT.PUT_LINE('blob1 contenttype: ' || dbms_lob.getcontenttype(blob1));
 set_media_type := 'photo/jpeg';
 DBMS_LOB.SETCONTENTTYPE(blob1, set_media_type);
 
 get_media_type := DBMS_LOB.GETCONTENTTYPE(blob1);
 DBMS_OUTPUT.PUT_LINE('Blob1 contenttype: ' || get_media_type);
 
 /*------------------------------------------------------------------*/
 /*---------------------- Fragment Operations -----------------------*/
 /*------------------ Print Before Fragment Operations --------------*/
 read_amt  := 40;
 DBMS_LOB.READ(clob1, read_amt, 1, readbuf);
 DBMS_OUTPUT.PUT_LINE(CHR(10)||'Clob1 before fragment insert: '|| readbuf);
 DBMS_OUTPUT.PUT_LINE(CHR(10)||'Length of clob1 before fragment operations: '|| dbms_lob.getlength(clob1));
 
 /*--------------------- Fragment Delete ----------------------------*/
 amount := 100;
 offset := 10;
 DBMS_LOB.FRAGMENT_DELETE(clob1, amount, offset);
 
 /*--------------------- Fragment Insert ----------------------------*/
 amount    := 29;
 offset    := 1;
 buffer    := '#Verify lob Delta operations#';
 DBMS_LOB.FRAGMENT_INSERT(clob1, amount, offset, buffer);
 
 /*---------------------- Fragment Move -----------------------------*/
 amount      := 29;
 src_offset  := 100;
 dest_offset := 1;
 
 -- fragment move
 DBMS_LOB.FRAGMENT_MOVE(clob1, amount, src_offset, dest_offset);
 
 /*---------------------- Fragment Replace --------------------------*/
 amount      := 25;
 amount_old  := 29;
 offset      := 100;
 buffer      := '$Verify fragment replace$';
 
 DBMS_LOB.FRAGMENT_REPLACE(clob1, amount_old, amount, offset,buffer);
 
 COMMIT;
 
 /*------------------ Verify After Fragment Operations --------------*/
 read_amt  := 40;
 DBMS_LOB.READ(clob1, read_amt, 1, readbuf);
 DBMS_OUTPUT.PUT_LINE(CHR(10)||'Clob1 after delta insert: '|| readbuf);
 DBMS_OUTPUT.PUT_LINE(CHR(10)||'Length of clob1 after fragment operations: '|| dbms_lob.getlength(clob1));
 
EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/