DBA Tips Archive for Oracle
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.
Temporary LOBs are created in your temporary tablespace.
When creating a temporary LOB, it is already set to empty. There is
no need, and in fact, you cannot use the
EMPTY_CLOB() functions with temporary LOBs.
To create a temporary LOB, use the
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
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.
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.
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-2017 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 email@example.com.
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: 48751