EMPTY_BLOB and EMPTY_CLOB Functions

You can use the special functions EMPTY_BLOB and EMPTY_CLOB in INSERT or UPDATE statements of SQL DML to initialize a NULL or non-NULL internal LOB to empty. These are available as special functions in Oracle8i SQL DML, and are not part of the DBMS_LOB package.

Before you can start writing data to an internal LOB using OCI or the DBMS_LOB package, the LOB column must be made non-null, that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value to empty by using the function EMPTY_BLOB in the VALUES clause of an INSERT statement. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB.

Syntax

FUNCTION EMPTY_BLOB() RETURN BLOB;
FUNCTION EMPTY_CLOB() RETURN CLOB;

Note: The parentheses are required syntax for both functions.

Parameters

None.

Return Values

EMPTY_BLOB returns an empty locator of type BLOB and EMPTY_CLOB returns an empty locator of type CLOB, which can also be used for NCLOBs.

Pragma

None.

Exceptions

An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.

Examples

The following example shows EMPTY_BLOB used with SQL DML:

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

The following example shows the correct and erroneous usage of EMPTY_BLOB and EMPTY_CLOB in PL/SQL programs:

DECLARE 
  loba         BLOB; 
  lobb         CLOB; 
  read_offset  INTEGER; 
  read_amount  INTEGER; 
  rawbuf       RAW(20); 
  charbuf      VARCHAR2(20);
BEGIN
  loba := EMPTY_BLOB(); 
  read_amount := 10; read_offset := 1;
  -- the following read will fail 
  dbms_lob.read(loba, read_amount, read_offset, rawbuf); 
   
  -- the following read will succeed;
  UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 
         1002 RETURNING c_lob INTO lobb;
dbms_lob.read(lobb, read_amount, read_offset, charbuf); 
  dbms_output.put_line('lobb value: ' || charbuf);