3.1 Before You Begin

Ensure that you go through the topics in this section before you start working with temporary LOBs.

• Creating Temporary LOBs
  This section describes how a temporary LOB gets created or generated in a client program.
• Handling Temporary LOBs on the Client Side
  You must consider the aspects discussed in this section while handling the temporary LOBs that are generated by the client programs.

3.1.1 Creating Temporary LOBs

This section describes how a temporary LOB gets created or generated in a client program.

You can create temporary LOB instances in one of the following ways:

• Declare a variable of the given LOB data type and pass it to the temporary LOB creation API. For example, in PL/SQL it is DBMS_LOB.CREATETEMPORARY, and in OCI it is OCILobCreateTemporary().
• Invoke a SQL or PL/SQL built-in function that produces a temporary LOB, for example, the SUBSTR function.
• Invoke a PL/SQL stored procedure or function that returns a temporary LOB as an OUT bind variable or a return value.

The temporary LOB instance exists in your application until it goes out of scope, your session terminates, or you explicitly free the instance.

Temporary LOBs reside in either the PGA memory or the temporary tablespace, depending on their size. Ensure that the PGA memory and the temporary tablespace have space that is large enough for the temporary LOBs used by your application.

Note:

• Oracle highly recommends that you release the temporary LOB instances to free the system resources. Failure to do so may cause accumulation of temporary LOBs and can considerably slow down your system.
• Starting with Oracle Database Release 21c, you do not need to check whether a LOB is temporary or persistent before releasing the temporary LOB. If you call the DBMS_LOB.FREETEMPORARY procedure or the OCILobFreeTemporary() function on a LOB, it will perform either of the following operations:
  • For a temporary LOB, it will release the LOB.
  • For a persistent LOB, it will do nothing (no-op).

See Also:

Performance Guidelines

3.1.2 Handling Temporary LOBs on the Client Side

You must consider the aspects discussed in this section while handling the temporary LOBs that are generated by the client programs.

Preventing Temporary LOB Accumulation

Every time a client program such as JDBC or OCI obtains a LOB locator from SQL or PL/SQL, and you suspect that it is producing a temporary LOB, then free the LOB as soon as your application has consumed the LOB. If you do not free the temporary LOB, then it will lead to accumulation of temporary LOBs, which can considerably slow down your system.

Note:

A temporary LOB duration is always upgraded to SESSION, when it is shipped to the client side.

For example, to prevent temporary LOB accumulation, an OCI application must call the OCILobFreeTemporary() function in the following scenarios:

• After getting a locator from a define during a SELECT statement or an OUT bind variable from a PL/SQL procedure or function. It is desirable that you free the temporary LOB as soon as you finish performing the required operations on it. If not, then you must free it before reusing the variable for fetching the next row or for another purpose.
• Before performing a pointer assignment, like <var1 = var2>, free the old temporary LOB in the variable <var1>.

LOB Assignment

You must take special care when assigning the OCILobLocator pointers in an OCI program while using the assignment (=) operator. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, the source and the target LOBs point to the same copy of data. This means that if you call the OCILobFreeTemporary() function on either one of them, then both variables will point to non-existent LOBs.

These semantics are different from using the LOB APIs, such as the OCILobLocatorAssign() function to perform assignments. When you use these APIs, the locators logically point to independent copies of data after assignment. This means that eventually the OCILobFreeTemporary() function must be called on each LOB descriptor separately, so that it frees all LOBs involved in the operation.

For temporary LOBs, before performing pointer assignments, you must ensure that you free any temporary LOB in the target LOB locator by calling the OCIFreeTemporary() function. In contrast, when the OCILobLocatorAssign() function is used, the original temporary LOB in the target LOB locator variable, if any, is freed automatically before the assignment happens.