Oracle offers a new built-in conversion function, TO_LOB, to convert a LONG or LONG RAW datatype to a LOB. You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement. This function was designed to allow you to migrate your LONG data to LOB columns, in anticipation of Oracle's discontinuing support for LONGs in a future release.
Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONGs, the LOB column must be of type CLOB (character large object) or NCLOB (NLS character large object). To convert LONG RAWs, the LOB column must be of type BLOB (binary large object).
Given the following tables:
CREATE TABLE long_table (n NUMBER, long_col LONG); CREATE TABLE lob_table (n NUMBER, lob_col CLOB);
use this function to convert LONG to LOB values as follows:
INSERT INTO lob_table SELECT n, TO_LOB(long_col) FROM long_table;
Oracle8 provided support for permanently storing large unstructured data by means of LOB datatypes; these are known as persistent LOBs . But many applications have a need for temporary LOBs that act like local variables but do not exist permanently in the database. This section discusses temporary LOBs and the use of the DBMS_LOB built-in package to manipulate these data structures.
Oracle8 i supports the creation, freeing, access, and update of temporary LOBs through the Oracle Call Interface (OCI) and DBMS_LOB calls. The default lifetime of a temporary LOB is a session, but such LOBs may be explicitly freed sooner by the application. Temporary LOBs are ideal as transient workspaces for data manipulation, and because no logging is done or redo records generated, they offer better performance than persistent LOBs. In addition, remember that whenever you rewrite or update a LOB, Oracle copies the entire LOB to a new segment. Applications that perform lots of piecewise operations on LOBs should see significant performance improvements with temporary LOBs.
A temporary LOB is empty when it is created -- you don't need to (and, in fact, you will not be able to) use the EMPTY_CLOB and EMPTY_BLOB functions to initialize LOB locators for a 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 if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.
Let's take a look at the DBMS_LOB programs provided to work with temporary LOBs, follow that with an example, and finish up by covering some of the administrative details.[ 1 ]
Before you can work with a temporary LOB, you need to create it with a call to the CREATETEMPORARY procedure. This program creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace. The header is:
DBMS_LOB.CREATETEMPORARY ( lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ], cache IN BOOLEAN, dur IN PLS_INTEGER := DBMS_LOB.SESSION);
The parameters are listed in Table 7.9 .
PROCEDURE DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ]);
After the call to FREETEMPORARY, the LOB locator that was freed ( lob_loc ) is marked as invalid. If an invalid LOB locator is assigned to another LOB locator through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.
The ISTEMPORARY function tells you if the LOB locator ( lob_loc ) points to a temporary or persistent LOB. The function returns an integer value: 1 means that it is a temporary LOB, 0 means that it is not (it's a persistent LOB instead):
DBMS_LOB.ISTEMPORARY ( lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ]) RETURN INTEGER;
Let's combine a number of these temporary LOB operations into a single example, found in the cretemplob.sql file on the disk. First, I create a directory; this is needed in order to reference a BFILE -- a file locator pointing to an operating system file outside the database:
/* Filename on companion disk: cretemplob.sql */ CREATE DIRECTORY trainings AS 'E:\Oracle8i-Training';
Next, I declare my local data structures: a named constant to hide the integer value used to represent "TRUE" by the ISTEMPORARY function, a BLOB to hold the locator to my temporary LOB, a BFILE that points to one of my PowerPoint presentations, and the number of bytes of that presentation that I want to load to my temporary LOB:
DECLARE /* Hide the 1/0 values for ISTEMPORARY. */ c_truetemp CONSTANT INTEGER := 1; tempBLOB BLOB; my_presentation BFILE := /* Note: Must pass the directory in uppercase. */ BFILENAME ('TRAININGS', 'collections.ppt'); lobLength INTEGER := 150000; BEGIN
Next, I create my temporary LOB and specify this call or block as the duration of the LOB. By taking this action, I avoid the need for an explicit call to DBMS_LOB. FREETEMPORARY to free the memory associated with the LOB:
DBMS_LOB.CREATETEMPORARY ( tempBLOB, TRUE, DBMS_LOB.CALL);
Now let's see if this new LOB really is a temporary one:
IF DBMS_LOB.ISTEMPORARY (tempBLOB) = c_truetemp THEN DBMS_OUTPUT.PUT_LINE ( 'It won''t be around for long...'); END IF;
On to the real work of the example: transfer a BFILE's contents to a temporary LOB. I will open the BFILE and then use the very convenient LOADFROMFILE procedure to do the transfer:
DBMS_LOB.OPEN ( my_presentation, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE ( tempBLOB, my_presentation, lobLength);
Notice that I open the BFILE but not the temporary LOB. That's because the OPEN step is optional for the temporary LOB. Now that I have transferred the contents, I will find out the length of the temporary LOB to confirm the transfer, and then close the BFILE:
lobLength := DBMS_LOB.GETLENGTH (tempBLOB); IF lobLength = 0 THEN DBMS_OUTPUT.PUT_LINE ('LOB is empty.'); ELSE DBMS_OUTPUT.PUT_LINE ( 'The length is ' || lobLength); END IF; DBMS_LOB.CLOSE(my_presentation); END; /
Temporary LOBs are handled quite differently from normal, persistent, internal LOBs. With temporary LOBs, there is no support for transaction management, consistent read operations, rollbacks, and so forth. There are various consequences to this lack of support:
Oracle offers a new V$ view called V$TEMPORARY_LOBS that shows how many cached and uncached LOBs exist per session. Your DBA can combine information from V$TEMPORARY_LOBS and the DBA_SEGMENTS data dictionary view to see how much space a session is using for temporary LOBs.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.