SQL semantics may yield temporary LOBs

One issue you will need to understand when applying SQL semantics to LOBs is that the result is often the creation of a temporary LOB. Think about applying the UPPER function to a CLOB:

DECLARE directions CLOB;BEGIN SELECT UPPER(falls_directions) INTO directions FROM waterfalls WHERE falls_name = 'Munising Falls';END;

Because they are potentially very large objects, CLOBs are stored on disk. Oracle can't uppercase the CLOB being retrieved because that would mean changing its value on disk, in effect changing a value that we simply want to retrieve. Nor can Oracle make the change to an in-memory copy of the CLOB because the value may not fit in memory, and also because what is being retrieved is only a locator that points to a value that must be on disk. The only option is for the database software to create a temporary CLOB in your temporary tablespace. The UPPER function then copies data from the original CLOB to the temporary CLOB, uppercasing the characters during the copy operation. The SELECT statement then returns a LOB locator pointing to the temporary CLOB, not to the original CLOB. There are two extremely important ramifications to all this:

· You cannot use the locator returned by a function or expression to update the original LOB. The directions variable in our example cannot be used to update the persistent LOB stored in the database because it really points to a temporary LOB returned by the UPPER function.

· Disk space and CPU resources are expended to create a temporary LOB, which can be of considerable size. We'll discuss this issue more in the next section, Section 12.5.8.2.

If we wish to retrieve an uppercase version of the directions to Munising Falls while still maintaining the ability to update the directions, we'll need to retrieve two LOB locators:

DECLARE directions_upper CLOB; directions_persistent CLOB;BEGIN SELECT UPPER(falls_directions), falls_directions INTO directions_upper, directions_persistent FROM waterfalls WHERE falls_name = 'Munising Falls';END;

Now we can access the uppercase version of the directions via the locator in directions_upper, and we can modify the original directions via the locator in directions_persistent. There's no performance penalty in this case from retrieving the extra locator. The performance hit comes from uppercasing the directions and placing them into a temporary CLOB. The locator in directions_persistent is simply plucked as-is from the database table.

In general, any character-string function to which you normally pass a VARCHAR2, and that normally returns a VARCHAR2 value, will return a temporary CLOB when you pass in a CLOB as input. Similarly, expressions that return CLOBs will most certainly return temporary CLOBs. Temporary CLOBs and BLOBs cannot be used to update the LOBs that you originally used in an expression or function.