Oracle PL/SQL Programming: Guide to Oracle8i Features

Oracle8 i offers several enhancements to the way you work with large objects (LOBs). It adds a new top-level function, TO_LOB, and also allows you to create and manipulate temporary LOBs.

7.5.1 The TO_LOB Function

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;

7.5.2 DBMS_LOB: Working with Temporary LOBs

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 createdyou 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]

^[1] For information about the DBMS_LOB package, see Oracle Built-in Packages ; the next edition of that book will describe temporary LOBs.

7.5.2.1 CREATETEMPORARY: Creating a temporary LOB

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.

Table 7.9. CREATETEMPORARY Parameters

7.5.2.2 FREETEMPORARY: Freeing the temporary LOB

The FREETEMPORARY procedure frees the temporary BLOB or CLOB in your default temporary tablespace. The header for this procedure is:

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.

7.5.2.3 ISTEMPORARY: Is it a temporary LOB?

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;

This function is designed to be called from within SQL; that, presumably, is the reason that Oracle did not define ISTEMPORARY to be a Boolean function.

7.5.2.4 Example

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 BFILEa 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; /

7.5.2.5 Managing temporary LOBs

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:

• If you encounter an error when processing with a temporary LOB, you must free that LOB and start your processing over again.

• You should not assign multiple LOB locators to the same temporary LOB. Lack of support for consistent read and undo operations can cause performance degradation with multiple locators.

• If a user modifies a temporary LOB while another locator is pointing to it, a copy (referred to by Oracle as a deep copy ) of that LOB is made. The different locators will then no longer see the same data. To minimize these deep copies, use the NOCOPY compiler hint whenever passing LOB locators as arguments.

• To make a temporary LOB permanent, you must call the DBMS_LOB.COPY program and copy the temporary LOB into a permanent one.

• Temporary LOB locators are unique to a session. You cannot pass a locator from one session to another (through a database pipe, for example), and make the associated temporary LOB visible in that other session.

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.