Creating a LOB

In the previous section, we used the following code to create a LOB locator:

--Insert a new row using EMPTY_CLOB( ) to create a LOB locatorINSERT INTO waterfalls (falls_name,falls_directions) VALUES ('Munising Falls',EMPTY_CLOB( ));

We then issued a SELECT statement to retrieve the new locator from a table. That sure seems like a cumbersome approach, doesn't it? You're probably wondering why we didn't just do the following:

directions := EMPTY_CLOB( );

It turns out that there is a very good reason why we took the approach we did. Remember that a CLOB variable (such as directions) really holds a LOB locator that points to the actual LOB data in the database. The key words to focus on here are in the database. LOBs do not exist in memory. They exist on disk in one or more of the database files. Furthermore, the physical details of how and where a given LOB is stored are specified as part of the table definition of the table containing the LOB. When we invoke the EMPTY_CLOB( ) function, we get an empty LOB (perhaps "empty LOB locator" would be a better term) that does not point to any specific location on disk. It's not until we store that empty LOB into a column in a database table that Oracle finishes filling in the blanks to produce a locator that we can use. When we insert our empty LOB into the waterfalls table, Oracle sees that the LOB locator is incomplete, decides on a location for the LOB based on storage information that the DBA provided as part of the waterfall table's definition, updates the LOB locator to point to that location, and finally stores the new locator as part of the table row that we are inserting. Only after all that can we actually work with the LOB. Of course, to get the newly completed LOB locator, we need to select it back again from the row that we just inserted.

It's worth noting that you don't necessarily need to embed the call to EMPTY_CLOB( ) within the INSERT statement. We can assign the results of EMPTY_CLOB to a CLOB variable, and then insert that variable into our table:

--Insert a new row using EMPTY_CLOB( ) to create a LOB locatordirections := EMPTY_CLOB( );INSERT INTO waterfalls (falls_name,falls_directions) VALUES ('Munising Falls',directions);

Note that even after this code executes, directions is still an empty LOB. A subsequent SELECT statement is necessary to retrieve the updated, nonempty LOB from the database.

When working with BLOBs, use EMPTY_BLOB( ) to create an empty BLOB. Use EMPTY_CLOB( ) for CLOBs and NCLOBs.

Beginning with Oracle8i, it is possible to work with LOBs without having to insert rows into your database. You do this using temporary LOBs, which are discussed in the later section, Section 12.5.7.