7.5.2.1 CREATETEMPORARY: Creating a temporary LOB

Before you can work with a temporary LOB, you need to create it with a call to the CREATETEMPORARY procedure. This program creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace. The header is:

DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],
   cache   IN BOOLEAN,
   dur     IN PLS_INTEGER := DBMS_LOB.SESSION);

The parameters are listed in Table 7.9 .

Table 7.9: CREATETEMPORARY Parameters

Parameter	Description
lob_loc	Receives the locator to the LOB.
cache	Specifies whether the LOB should be read into the buffer cache.
dur	Controls the duration of the LOB. The dur argument can be one of the following two named constants: DBMS_LOB.SESSION Specifies that the temporary LOB created should be cleaned up (memory freed) at the end of the session. This is the default. DBMS_LOB.CALL Specifies that the temporary LOB created should be cleaned up (memory freed) at the end of the current program call in which the LOB was created.

7.5.2.2 FREETEMPORARY: Freeing the temporary LOB

The FREETEMPORARY procedure frees the temporary BLOB or CLOB in your default temporary tablespace. The header for this procedure is:

PROCEDURE DBMS_LOB.FREETEMPORARY (
   lob_loc IN OUT NOCOPY 
      [ BLOB | CLOB CHARACTER SET ANY_CS ]); 

After the call to FREETEMPORARY, the LOB locator that was freed ( lob_loc ) is marked as invalid. If an invalid LOB locator is assigned to another LOB locator through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.

7.5.2.3 ISTEMPORARY: Is it a temporary LOB?

The ISTEMPORARY function tells you if the LOB locator ( lob_loc ) points to a temporary or persistent LOB. The function returns an integer value: 1 means that it is a temporary LOB, 0 means that it is not (it's a persistent LOB instead):

DBMS_LOB.ISTEMPORARY (
   lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ])
  RETURN INTEGER;

This function is designed to be called from within SQL; that, presumably, is the reason that Oracle did not define ISTEMPORARY to be a Boolean function.

7.5.2.4 Example

Let's combine a number of these temporary LOB operations into a single example, found in the cretemplob.sql file on the disk. First, I create a directory; this is needed in order to reference a BFILE -- a file locator pointing to an operating system file outside the database:

/* Filename on companion disk: cretemplob.sql */
CREATE DIRECTORY trainings AS 'E:\Oracle8i-Training';

Next, I declare my local data structures: a named constant to hide the integer value used to represent "TRUE" by the ISTEMPORARY function, a BLOB to hold the locator to my temporary LOB, a BFILE that points to one of my PowerPoint presentations, and the number of bytes of that presentation that I want to load to my temporary LOB:

DECLARE
   /* Hide the 1/0 values for ISTEMPORARY. */
   c_truetemp CONSTANT INTEGER := 1;
   
   tempBLOB BLOB;
   
   my_presentation BFILE :=
      /* Note: Must pass the directory in uppercase. */ 
      BFILENAME ('TRAININGS', 'collections.ppt');
      
   lobLength INTEGER := 150000;
BEGIN

Next, I create my temporary LOB and specify this call or block as the duration of the LOB. By taking this action, I avoid the need for an explicit call to DBMS_LOB. FREETEMPORARY to free the memory associated with the LOB:

   DBMS_LOB.CREATETEMPORARY (
      tempBLOB, TRUE, DBMS_LOB.CALL);

Now let's see if this new LOB really is a temporary one:

      IF DBMS_LOB.ISTEMPORARY (tempBLOB) = c_truetemp
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'It won''t be around for long...');
   END IF;

On to the real work of the example: transfer a BFILE's contents to a temporary LOB. I will open the BFILE and then use the very convenient LOADFROMFILE procedure to do the transfer:

   DBMS_LOB.OPEN (
      my_presentation, DBMS_LOB.LOB_READONLY);
   
   DBMS_LOB.LOADFROMFILE (
      tempBLOB, my_presentation, lobLength);

Notice that I open the BFILE but not the temporary LOB. That's because the OPEN step is optional for the temporary LOB. Now that I have transferred the contents, I will find out the length of the temporary LOB to confirm the transfer, and then close the BFILE:

   lobLength := DBMS_LOB.GETLENGTH (tempBLOB);
   IF lobLength = 0 
   THEN
       DBMS_OUTPUT.PUT_LINE ('LOB is empty.');
   ELSE
       DBMS_OUTPUT.PUT_LINE (
          'The length is ' || lobLength);
   END IF;
   
   DBMS_LOB.CLOSE(my_presentation);
END;
/

7.5.2.5 Managing temporary LOBs

Temporary LOBs are handled quite differently from normal, persistent, internal LOBs. With temporary LOBs, there is no support for transaction management, consistent read operations, rollbacks, and so forth. There are various consequences to this lack of support:

• If you encounter an error when processing with a temporary LOB, you must free that LOB and start your processing over again.

• You should not assign multiple LOB locators to the same temporary LOB. Lack of support for consistent read and undo operations can cause performance degradation with multiple locators.

• If a user modifies a temporary LOB while another locator is pointing to it, a copy (referred to by Oracle as a deep copy ) of that LOB is made. The different locators will then no longer see the same data. To minimize these deep copies, use the NOCOPY compiler hint whenever passing LOB locators as arguments.

• To make a temporary LOB permanent, you must call the DBMS_LOB.COPY program and copy the temporary LOB into a permanent one.

• Temporary LOB locators are unique to a session. You cannot pass a locator from one session to another (through a database pipe, for example), and make the associated temporary LOB visible in that other session.

Oracle offers a new V$ view called V$TEMPORARY_LOBS that shows how many cached and uncached LOBs exist per session. Your DBA can combine information from V$TEMPORARY_LOBS and the DBA_SEGMENTS data dictionary view to see how much space a session is using for temporary LOBs.