DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



Temporary LOBs, as the name would suggest, are not permanent and hence not stored in the database. Normally, the temporary LOB would exist throughout the entire duration of a user's session. However, it is possible to specify that you want the temporary LOB to exist only during the duration of the current program call at the time of its creation.

Temporary LOBs are used just like any other local variable and are used when you need to perform some operations on LOB data without having to store it in the database.


The operations you can perform on temporary LOBs are create, access, update, and then freeing the temporary LOB. All of the procedures and functions for workings with temporary LOBs are located within the DBMS_LOB PL/SQL package.


For performance reasons, there is no logging or redo information generated for temporary LOBs.

Where are Temporary LOBs Stored?

Temporary LOBs are created in your temporary tablespace.

Creating a Temporary LOB

When creating a temporary LOB, it is already set to empty. There is no need, and in fact, you cannot use the EMPTY_BLOB() or EMPTY_CLOB() functions with temporary LOBs.

To create a temporary LOB, use the DBMS_LOB.CREATETEMPORARY() procedure.


Looking at the above procedure, you can see that that it will create a temporary LOB with the locator being returned in the lob_loc variable. Also created with the temporary LOB is a temporary LOB index in the default temporary tablespace. The duration parameter is used to specify the lifetime of the temporary LOB and defaults to the session (DBMS_LOB.SESSION). It is also possible to set the duration parameter to the current program by using the integer DBMS_LOB.CALL.

Freeing a Temporary LOB

Use the freetemporary() procedure to free a created CLOB or BLOB in your temporary tablespace. After calling this procedure, the lob locator associated with the temporary LOB is marked invalid.



If you were to subsequently assign this lob locator to another lob locator, the latter one is also freed and marked invalid.

Check if a LOB is Temporary

Use the istemporary() function to determine if a given lob locator points to a temporary LOB or a persistent LOB. This function returns an integer value of 1 for a temporary LOB and 0 for a persistent LOB.


Temporary LOB Example

CREATE OR REPLACE PROCEDURE testTemporaryLob AS temporaryLob CLOB; amount NUMBER; offset NUMBER := 1; buffer VARCHAR2(100) := 'Testing a temporary LOB. '; buffer2 VARCHAR2(100) := 'Appended data.'; BEGIN DBMS_OUTPUT.ENABLE(1000000); -- -------------------- -- CREATE TEMPORARY LOB -- -------------------- DBMS_LOB.CREATETEMPORARY( lob_loc => temporaryLob , cache => true , dur => dbms_lob.session ); -- ------------------------------- -- TEST TO SEE IF LOB IS TEMPORARY -- ------------------------------- IF (DBMS_LOB.ISTEMPORARY(temporaryLob) = 1) THEN DBMS_OUTPUT.PUT_LINE('This is a temporary LOB.'); ELSE DBMS_OUTPUT.PUT_LINE('This is a persistent LOB.'); END IF; -- ----------------------------------- -- OPEN TEMPORARY LOB FOR READ / WRITE -- ----------------------------------- DBMS_LOB.OPEN( lob_loc => temporaryLob , open_mode => DBMS_LOB.LOB_READWRITE ); -- ----------------------------------- -- WRITE SMALL BUFFER TO TEMPORARY LOB -- ----------------------------------- DBMS_LOB.WRITE( lob_loc => temporaryLob , amount => LENGTH(buffer) , offset => offset , buffer => buffer ); -- -------------------------------------- -- APPEND A SMALL BUFFER TO TEMPORARY LOB -- -------------------------------------- DBMS_LOB.WRITEAPPEND( lob_loc => temporaryLob , amount => LENGTH(buffer2) , buffer => buffer2 ); -- ----------------------------------- -- GET CURRENT LENGTH OF TEMPORARY LOB -- ----------------------------------- DBMS_OUTPUT.PUT_LINE( 'Current length of temporary LOB: ' || DBMS_LOB.GETLENGTH(temporaryLob) ); -- ----------------------------------- -- PRINT CONTENTS OF THE TEMPORARY LOB -- ----------------------------------- DBMS_OUTPUT.PUT_LINE('Current content of temporary LOB:'); DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(temporaryLob, DBMS_LOB.GETLENGTH(temporaryLob), 1)); -- -------------------------- -- CLOSE / FREE TEMPORARY LOB -- -------------------------- DBMS_LOB.CLOSE(lob_loc => temporaryLob); DBMS_LOB.FREETEMPORARY(lob_loc => temporaryLob); END; / Procedure created.

SQL> exec testTemporaryLob This is a temporary LOB. Current length of temporary LOB: 39 Current content of temporary LOB: Testing a temporary LOB. Appended data. PL/SQL procedure successfully completed.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Sunday, 11-Mar-2012 18:58:59 EDT
Page Count: 49631