Creating Tables that Contain LOBs

When creating tables that contain LOBs use the guidelines described in the following sections:

• Initializing Internal LOBs to NULL or Empty

• Initializing Internal LOB Columns to a Value

• Initializing External LOBs to NULL or a File Name

• Defining tablespace and storage characteristics. See Chapter 7, "Modeling and Design", "Defining Tablespace and Storage Characteristics for Internal LOBs".

Initializing Internal LOBs to NULL or Empty

You can set an internal LOB -- that is, a LOB column in a table, or a LOB attribute in an object type defined by you-- to be NULL or empty:

• Setting an Internal LOB to NULL: A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.

• Setting an Internal LOB to Empty: By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator which you can use to populate the LOB with data via one of the six programmatic environments, such as OCI or PL/SQL(DBMS_LOB). See Chapter 3, "LOB Programmatic Environments".

These options are discussed in more detail below.

As discussed below, an external LOB (i.e. BFILE) can be initialized to NULL or to a filename.

Setting an Internal LOB to NULL

You may want to set the internal LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT and/or if you want to issue a SELECT statement at some later time such as:

SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NOT NULL; 

because you want to see all the voice-over segments that have been recorded, or

SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NULL; 

if you wish to establish which segments still have to be recorded.

You Cannot Call OCI or DBMS_LOB Functions on a NULL LOB

However, the drawback to this approach is that you must then issue a SQL UPDATE statement to reset the null LOB column -- to EMPTY_BLOB() or EMPTY_CLOB() or to a value (e.g. 'Denzel Washington') for internal LOBs, or to a filename for external LOBs.

The point is that you cannot call one of the six programmatic environments (for example, OCI or PL/SQL (DBMS_LOB) functions on a LOB that is NULL. These functions only work with a locator, and if the LOB column is NULL, there is no locator in the row.

Setting an Internal LOB to Empty

If you do not want to set an internal LOB column to NULL, you can set the LOB value to empty using the function EMPTY_BLOB () or EMPTY_CLOB() in the INSERT statement:

INSERT INTO a_table VALUES (EMPTY_BLOB());
 

Even better is to use the returning clause (thereby eliminating a round trip that is necessary for the subsequent SELECT), and then immediately call OCI or the PL/SQL DBMS_LOB functions to populate the LOB with data.

DECLARE
   Lob_loc  BLOB;
BEGIN
   INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc;
   /* Now use the locator Lob_loc to populate the BLOB with data */
END;

Example Using Table Multimedia_tab

You can initialize the LOBs in Multimedia_tab by using the following INSERT statement:

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

This sets the value of story, flsub, frame and sound to an empty value, and sets photo, and music to NULL.

Initializing Internal LOB Columns to a Value

Alternatively, LOB columns, but not LOB attributes, may be initialized to a value. Which is to say -- internal LOB attributes differ from internal LOB columns in that LOB attributes may not be initialized to a value other than NULL or empty.

Note that you can initialize the LOB column to a value that contains more than 4K data. See Chapter 7.

Initializing External LOBs to NULL or a File Name

An external LOB (BFILE) can be initialized to NULL or to a filename via the BFILENAME() function.

See Chapter 11, "External LOBs (BFILEs)", "Directory Object" -- "Initializing a BFILE Locator".