Initializing LOB Locators

When Do I Use EMPTY_BLOB() and EMPTY_CLOB()?

Question

When must I use EMPTY_BLOB() and EMBPTY_CLOB()? I always thought it was mandatory for each insert of a CLOB or BLOB to initialize the LOB locator first with either EMPTY_CLOB() or EMPTY_BLOB().

Answer

In Oracle8i release 8.1.5, you can initialize a LOB with data via the insert statement as long as the data is <4K. This is why your insert statement worked. Note that you can also update a LOB with data that is <4K via the UPDATE statement. If the LOB is larger than 4K perform the following steps:

1. Insert into the table initializing the LOB via EMPTY_BLOB() or EMPTY_CLOB() and use the returning clause to get back the locator

2. For LOB attributes, call ocilobwrite() to write the entire data to the LOB. For other than LOB attributes, you can insert all the data via the INSERT statement.

Note the following:

• We've removed the <4K restriction and you can insert >4K worth of data into the LOB via the insert or even the update statement for LOB columns. Note however, that you cannot initialize a LOB attribute which is part of an object type with data and you must use EMPTY_BLOB()/EMPTY_CLOB().

• Also you cannot use >4K as the default value for a LOB even though you can use >4k when inserting or updating the LOB data.

• Initializing the LOB value with data or via EMPTY_BLOB()/EMPTY_CLOB() is orthogonal to how the data is stored. If the LOB value is less than approximately 4K, then the value is stored inline (as long as the user doesn't specify DISABLE STORAGE IN ROW) and once it grows larger than 4K, it is moved out of line.

How Do I Initialize a BLOB Attribute Using EMPTY_BLOB() in Java?

Question

From java we want to insert a complete object with a BLOB attribute into an Oracle8.1.5 object table. The problem is - in order to do that - we have somehow to initialize the blob attribute with EMPTY_BLOB(). Is there any way to initialize the BLOB attribute with EMPTY_BLOB() in java ?

What I am doing at the moment is:

First I insert the object with null in the BLOB attribute. Afterwards I update the object with an EMPTY_BLOB(), then select it again, get the BLOB locator and finally write my BLOB.

Is this the only way it works ? Is there a way to initialize the BLOB directly in my toDatum method of the Custom Datum interface implementation?

Answer

Here is the SQLJ equivalent...

    BLOB myblob = null;  
    #sql { select empty_blob() into :myblob from dual } ;  

and use myblob in your code wherever the BLOB needed to be initialized to null.

See also the question and answer under the section, "JDBC, JPublisher and LOBs", "How Do I setData to EMPTY_BLOB() Using JPublisher?"