Ensure that you go through the topics in this section before you start working with temporary LOBs.
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
- Invoke a SQL or PL/SQL built-in function that produces a temporary LOB, for example, the
- Invoke a PL/SQL stored procedure or function that returns a temporary LOB as
OUTbind 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.
- 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.FREETEMPORARYprocedure 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
SELECTstatement or an
OUTbind 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
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.