One is All You Need

I just recently authored a PL/SQL Server Page designed to funnel large amounts of data via the htp.p function to a web application. Because the volume of data could be very large, I wanted to minimize the individual calls to the htp.p function (which can only take a maximum of 32767 characters per call).

The answer was obviously CLOBS! I proceeded to accumulate the full multi-megabyte response in a CLOB by concatenating the individual VARCHAR2 results line by line as they came out of the database.

Now I’ll be the first to admit that my experience with LOBs in Oracle has been very limited. And with the onset of SecureFiles in 11g, maybe I will never really become very proficient with LOBs. But in my LOB naivety, I assumed that if it COULD be done, it would probably work fine. You would think that after 15 years of Oracle development, I would know better…nope.

DECLARE
    bigLob CLOB;
    CURSOR getVarchars2 IS
         SELECT singleColumn FROM table;
BEGIN
FOR v_rec IN getVarchars2 LOOP
    bigClob := bigClob||v_rec.singleRow||linebreak;
END LOOP;
    doStuffWithTheCLOB(bigCLOB);
END;

It looks so innocent. Append the VARCHAR2 to the end of the CLOB. Let the database do the data type conversion from VARCHAR2 to CLOB. No problema! 10 rows, 100 rows, 1000 rows, all working just fine. Then came the request for 44K rows of data. The database became very angry and would not cooperate (“Stop asking for 44k rows!”). The user community became very angry and would not cooperate (“We need our 44k rows!”).

The solution is of course found in an obscure part of the Oracle documentation.

Here are the relevant entries:
• When Possible, Read/Write Large Data Chunks at a Time: Because LOBs are big, you can obtain the best performance by reading and writing large pieces of a LOB value at a time. This helps in several respects:
    o Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, then the chunk is also stored in the redo log.

• If you use the newly provided enhanced SQL semantics functionality in your applications, then there will be many more temporary LOBs created silently in SQL and PL/SQL than before. Ensure that temporary tablespace for storing these temporary LOBs is large enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
     o SQL functions on LOBs
     o PL/SQL built-in character functions on LOBs
     o Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.
     o Perform a LONG-to-LOB migration

I highly recommend looking at this chapter in the LOB documentation prior to working with LOBs in SQL and PL/SQL.

Greg Pike