Programmatic Environments

Oracle9i supports the definition, creation, deletion, access, and update of temporary LOBs in the following programmatic environments or "interfaces":

• PL/SQL, using the DBMS_LOB package

• C/C++, using PRO*C precompiler

• COBOL, using Pro*COBOL precompiler

• C, using OCI

• Java, using JDBC

Locators

The 'interfaces' listed above, operate on temporary LOBs through locators in the same way that they do for permanent LOBs. Since temporary LOBs are never part of any table, you cannot use SQL DML to operate on them. You must manipulate them using the DBMS_LOB package, OCI, or the other programmatic interfaces.

Temporary LOB Locators Can be IN Values

SQL support for temporary LOBs is available in that temporary LOB locators can be used as IN values, with values accessed through a locator. Specifically, they can be used as follows:

• As a value in a WHERE clause for INSERT, UPDATE, DELETE, or SELECT. For example:

  SELECT pattern FROM composite_image WHERE temp_lob_pattern_id  = 
  somepattern_match_function(lobvalue);
  
  

• As a variable in a SELECT INTO... statement. For example:

  SELECT PermanentLob INTO TemporaryLob_loc FROM Demo_tab WHERE Column1 := 1; 
  

  Note: Selecting a permanent LOB into a LOB locator that points to a temporary LOB will cause the locator to point to a permanent LOB. It does not cause a copy of the permanent LOB to be put in the temporary LOB.

Can You Use the Same Functions for Temporary and Internal Persistent LOBs?

Compare the use case model diagrams for temporary LOBs with the Figure 11-1, "Use Case Model Diagram: Internal Temporary LOBs (part 1 of 2)", and Figure 11-2, "Use Case Model Diagram: Internal temporary LOBs (part 2 of 2)". Observe that you can use the following functions for internal persistent LOBs and temporary LOBs:

• DBMS_LOB package PL/SQL procedures (COMPARE, INSTR, SUBSTR)

• DBMS_LOB package PL/SQL procedures and corresponding OCI functions (Append, Copy, Erase, Getlength, Loadfromfile, Read, Trim, Write, WriteAppend).

• OCI functions (OCILobLocatorAssign, OCILobLocatorIsInit, and so on).

In addition, you can use the ISTEMPORARY function to determine if a LOB is temporarily based on its locator.

Note: One thing to keep in mind is that temporary LOBs do not support transactions and consistent reads.

DBMS_LOB.createtemporary() Parameter is a Hint

The DBMS_LOB.createtemporary() call takes a duration parameter. This parameter is merely a hint and is not strictly enforced.

Temporary LOB Data is Stored in Temporary Tablespace

Temporary LOBs are not stored permanently in the database like other data. The data is stored in temporary tablespaces and is not stored in any tables. This means you can CREATE an internal temporary LOB (BLOB,CLOB, NCLOB) on the server independent of any table, but you cannot store that LOB.

Since temporary LOBs are not associated with table schema, there is no meaning to the terms "inline" and "out-of-line" for temporary LOBs.

Note: All temporary LOBs reside on the server. There is no support for client-side temporary LOBs.

Lifetime and Duration of Temporary LOBs

The default lifetime of a temporary LOB is a session.

The interface for creating temporary LOBs includes a parameter that lets you specify the default scope of the life of the 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 the database crashes, all temporary LOBs are deleted.

OCI Can Group Temporary LOBs into Logical Buckets

OCI users can group temporary LOBs together into a logical bucket.

"OCIDuration" represents a store for temporary LOBs. There is a default duration for every session into which temporary LOBs are placed if you do not specify a specific duration. The default duration ends when your session ends. Also, you can perform an OCIDurationEnd operation that frees all OCIDuration contents.

Memory Handling

LOB Buffering and CACHE, NOCACHE, CACHE READS

Temporary LOBs are especially useful when you want to perform transformational operations on a LOB -- such as morphing an image, or changing a LOB from one format to another -- and then return it to the database.

These transformational operations can use LOB Buffering. You can specify CACHE,NOCACHE,or CACHE READS for each temporary LOB, and FREE an individual temporary LOB when you have no further need for it.

Temporary Tablespace

Your temporary tablespace is used to store temporary LOB data. Data storage resources are controlled by the DBA through control of a user's access to temporary tablespaces, and by the creation of different temporary tablespaces.

Explicitly Free Temporary LOB Space to Reuse It

Memory usage increases incrementally as the number of temporary LOBs grows. You can reuse temporary LOB space in your session by freeing temporary LOBs explicitly.

• When the Session Finishes: Explicitly freeing one or more temporary LOBs does not result in all of the space being returned to the temporary tablespace for general re-consumption. Instead, it remains available for reuse in the session.

• When the Session Dies: If a process dies unexpectedly or the database crashes, the space for temporary LOBs is freed along with the deletion of the temporary LOBs. In all cases, when a user's session ends, space is returned to the temporary tablespace for general reuse.

Selecting a Permanent LOB INTO a Temporary LOB Locator

We previously noted that if you perform the following:

SELECT permanent_lob INTO temporary_lob_locator FROM y_blah WHERE x_blah

the temporary_lob_locator will get overwritten with the permanent_lob's locator. The temporary_lob_locator now points to the LOB stored in the table.

Note: Unless you saved the temporary_lob's locator in another variable, you will lose track of the LOB that temporary_lob_locator originally pointed at before the SELECT INTO operation. In this case the temporary LOB will not get implicitly freed. If you do not wish to waste space, explicitly free a temporary LOB before overwriting it with a permanent LOB locator.

Since CR and rollbacks are not supported for temporary LOBs, you will have to free the temporary LOB and start over again if you run into an error.

See Also: Chapter 7, "Modeling and Design", "LOB Storage" and Chapter 9, "LOBS: Best Practices".

Locators and Semantics

Creation of a temporary LOB instance by a user causes the engine to create, and return a locator to LOB data. Temporary LOBs do not support any operations that are not supported for persistent LOB locators, but temporary LOB locators have specific features.