The previously listed types of LOBs have been permanently storing large amounts of unstructured data by means of the various LOB datatypes. Such LOBs are known as persistent LOBs. Many applications have a need for temporary LOBs that act like local variables but do not exist permanently in the database. This section discusses temporary LOBs and the use of the DBMS_LOB built-in package to manipulate them.

Oracle8i and subsequent releases of Oracle’s database software support the creation, freeing, access, and update of temporary LOBs through the Oracle Call Interface (OCI) and DBMS_LOB calls. The default lifetime of a temporary LOB is the lifetime of the session that created it, but such LOBs may be explicitly freed sooner by the application. Temporary LOBs are ideal as transient workspaces for data manipulation, and because no logging is done and no redo records are generated, they offer better performance than persistent LOBs do. In addition, whenever you rewrite or update a LOB, Oracle copies the entire LOB to a new segment. By avoiding all the associated redo and logging, applications that perform lots of piecewise operations on LOBs should see significant performance improvements with temporary LOBs.

A temporary LOB is empty when it is created—you don’t need to (and, in fact, you will not be able to) use the EMPTY_CLOB and EMPTY_BLOB functions to initialize LOB locators for a temporary LOB. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted and the space for temporary LOBs is freed.

Temporary LOBs are just like persistent LOBs in that they exist on disk inside your database. Don’t let the word “temporary” fool youinto thinking that they are memory structures. Temporary LOBs are written to disk, but instead of being associated with a specific LOB column in a specific table, they are written to disk in your session’s temporary tablespace. Thus, if you use temporary LOBs, you need to make sure that your temporary tablespace is large enough to accommodate them.

Let’s examine the processes for creating and freeing temporary LOBs. Then look at how you can test to see whether a LOB locator points to a temporary or a permanent LOB. The end of this topic will be covering some of the administrative details to consider when you’re working with temporary LOBs.

Creating a Temporary LOB

Before you can work with a temporary LOB, you need to create it. One way to do this is with a call to the DBMS_LOB.CREATETEMPORARY procedure. This procedure 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 to DBMS_LOB.CREATETEMPORARY are listed in Table 1.

Table 1: CREATETEMPORARY Parameters

ParameterDescription
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.

Another way to create a temporary LOB is to declare a LOB variable in your PL/SQL code and assign a value to it. For example, the following code creates both a temporary BLOB and a temporary CLOB:

DECLARE
  temp_clob CLOB;
  temp_blob BLOB;
BEGIN
  --Assigning a value to a null CLOB or BLOB variable causes
  --PL/SQL to implicitly create a session-duration temporary
  --LOB for you.
  temp_clob :='http://www.nps.gov/piro/';
  temp_blob := HEXTORAW('7A');
END;

There isn't really a strong preference as to which method you should use to create a temporary LOB, but it is believed that the use of DBMS_LOB.CREATETEMPORARY makes the intent of your code a bit more explicit.

Freeing a Temporary LOB

The DBMS_LOB.FREETEMPORARY procedure frees a 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 ]);

In the following example, two temporary LOBs are created. Then explicitly freed:

DECLARE
  temp_clob CLOB;
  temp_blob BLOB;
BEGIN
  --Assigning a value to a null CLOB or BLOB variable causes
  --PL/SQL to implicitly create a session-duration temporary
  --LOB for you.
  temp_clob :=' http://www.exploringthenorth.com/alger/alger.html';
  temp_blob := HEXTORAW('7A');
  
  DBMS_LOB.FREETEMPORARY(temp_clob);
  DBMS_LOB.FREETEMPORARY(temp_blob);
END;

After a call to FREETEMPORARY, the LOB locator that was freed (lob_loc in the previous specification) 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.

Checking to See Whether a LOB is Temporary

The ISTEMPORARY function tells you if the LOB locator (lob_loc in the following specification) points to a temporary or a persistent LOB. The function returns an integer value: 1 means that it is a temporary LOB, and 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 Oracle did not define ISTEMPORARY to be a Boolean function.

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 of 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 you’re 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 LOB.

 

  • 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