PL/SQL and CLOBs

It can be helpful to think that CLOBs can be treated in ways similar to operating system files. They can be written to (dbms_lob.write, dbms_lob.writeAppend), they can be read in a fashion akin to a seek function (dbms_lob.substr) and so on.

Figure 1.0 shows some PL/SQL code to illustrate this.

CREATE TABLE tab1 (narrative CLOB);

DECLARE
    l_clob CLOB;
BEGIN
    INSERT INTO tab1 VALUES (EMPTY_CLOB())
    RETURNING narrative
    INTO      l_clob;
    --
    dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
    --
    FOR i IN 1 .. 10
    LOOP
       dbms_lob.writeAppend(l_clob, 32000, RPAD( '*', 32000, '*' ));
    END LOOP;
    --
    dbms_lob.close(l_clob);
    COMMIT;
END;

sql> SELECT LENGTH(narrative) from tab1;

LENGTH(NARRATIVE)
-----------------
           320000

1 row selected.

sql>

Notice the explicit opening and closing of the LOBs. While it is possible to omit these operations and ORACLE will happily carry out these operations on your behalf, it is worth reading the ORACLE documentation on this subject. Here is a section from the Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2):

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

When calling DBMS_LOB.WRITE from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.

It is not mandatory that you wrap the LOB operation inside the Open/Close APIs. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB is closed, it updates
the functional and domain indexes on the LOB column.

If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.

This is like having fwrite operations that automatically perform fopen and fclose automatically on each call rather than just the once before and after all writes are completed.

Benchmarking LOB writes with and without explicit open and close operations reveals this to be quite true. For large numbers, without explicit opening and closing, latching can become horrendously bad, not to mention performance. This impacts scalability big-time!