|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Basic LOB Components, 5 of 5
When creating tables that contain LOBs use the guidelines described in the following sections:
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:
NULLhas 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.
LOBstored in a table is a
LOBof zero length that has a locator. So, if you
SELECTfrom an empty
LOBcolumn or attribute, you get back a locator which you can use to populate the
LOBwith data via one of the six programmatic environments, such as OCI or
PL/SQL(DBMS_LOB). See Chapter 3, "LOB Support in Different Programmatic Environments".
These options are discussed in more detail below.
As discussed below, an external
BFILE) can be initialized to
NULL or to a filename.
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:
because you want to see all the voice-over segments that have been recorded, or
if you wish to establish which segments still have to be recorded.
However, the drawback to this approach is that you must then issue a SQL
UPDATE statement to reset the null
LOB column -- to
EMPTY_CLOB() or to a value (for example,'Denzel Washington') for internal
LOBs, or to a filename for external
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.
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
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;
You can initialize the
Multimedia_tab by using the following
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
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.
An external LOB (BFILE) can be initialized to NULL or to a filename using the BFILENAME() function.