When a LOB is returned from a SQL or PL/SQL built-in function, then the result returned is a temporary LOB. Similarly, a LOB returned from a user-defined PL/SQL function or procedure, as a value or an OUT parameter, may be a temporary LOB.
In PL/SQL, a temporary LOB has the same lifetime (duration) as the local PL/SQL program variable in which it is stored. It can be passed to subsequent SQL or PL/SQL
VARCHAR2 functions or queries as a PL/SQL local variable. The temporary LOB goes out of scope at the end of the program block at which time, the LOB is freed. These are the same semantics as those for PL/SQL
VARCHAR2 variables. At any time, nonetheless, you can use a
DBMS_LOB.FREETEMPORARY() call to release the resources taken by the local temporary LOBs.
Note:If a SQL or PL/SQL function returns a temporary LOB, or if a LOB is an OUT parameter for a PL/SQL function or procedure, then you must free it as soon as you are done with it. Failure to do so may cause temporary LOB accumulation and can considerably slow down your system.
The following example illustrates implicit creation of temporary LOBs using SQL built-in functions:
DECLARE vc1 VARCHAR2(32000); lb1 CLOB; lb2 CLOB; BEGIN SELECT clobCol1 INTO vc1 FROM tab WHERE colID=1; -- lb1 is a temporary LOB SELECT clobCol2 || clobCol3 INTO lb1 FROM tab WHERE colID=2; lb2 := vc1|| lb1; -- lb2 is a still temporary LOB, so the persistent data in the database -- is not modified. An update is necessary to modify the table data. UPDATE tab SET clobCol1 = lb2 WHERE colID = 1; DBMS_LOB.FREETEMPORARY(lb2); -- Free up the space taken by lb2
<... some more queries ...>
END; -- at the end of the block, lb1 is automatically freed
Here is another example of implicit creation of temporary LOBs using PL/SQL built-in functions.
1 DECLARE 2 myStory CLOB; 3 revisedStory CLOB; 4 myGist VARCHAR2(100); 5 revisedGist VARCHAR2(100); 6 BEGIN 7 -- select a CLOB column into a CLOB variable 8 SELECT Story INTO myStory FROM print_media WHERE product_id=10; 9 -- perform VARCHAR2 operations on a CLOB variable 10 revisedStory := UPPER(SUBSTR(myStory, 100, 1)); 11 -- revisedStory is a temporary LOB 12 -- Concat a VARCHAR2 at the end of a CLOB 13 revisedStory := revisedStory || myGist; 14 -- The following statement raises an error because myStory is 15 -- longer than 100 bytes 16 myGist := myStory; 17 END; /
Note that in the preceding example:
- In line number 7, a temporary
CLOBis implicitly created and is pointed to by the
- In line number 13,
myGistis appended to the end of the temporary LOB, which has the same effect as the following code snippet:
DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));
In some scenarios, implicitly created temporary LOBs in PL/SQL statements can change the representation of previously defined LOB locators. The following code snippet explains this scenario:
Change in Locator-Data Linkage
1 DECLARE 2 myStory CLOB; 3 amt number:=100; 4 buffer VARCHAR2(100):='some data'; 5 BEGIN 6 -- select a CLOB column into a CLOB variable 7 SELECT Story INTO myStory FROM print_media WHERE product_id=10; 8 DBMS_LOB.WRITE(myStory, amt, 1, buf); 9 -- write to the persistent LOB in the table 10 11 myStory:= UPPER(SUBSTR(myStory, 100, 1)); 12 -- perform VARCHAR2 operations on a CLOB variable, temporary LOB created. 13 -- Changes are not reflected in the database table from this point on. 14 15 UPDATE print_media SET Story = myStory WHERE product_id = 10; 16 -- an update is necessary to synchronize the data in the table. 17 END;
In the preceding example,
myStory represents a persistent LOB column in the
print_media table. The
DBMS_LOB.WRITE procedure writes the data directly to the table without an
UPDATE statement in the code.
Subsequently in line number 11, a temporary LOB is created and assigned to
myStory is now used like a local
VARCHAR2 variable. The LOB locator
myStory now points to the newly-created temporary LOB.
Therefore, modifications to
myStory are no longer reflected in the database. To propagate the changes to the database table now, you must use an
UPDATE statement. Note that for the previous persistent LOB, the
UPDATE statement is not required.
See Also:Working with Remote LOBs in SQL and PL/SQL for PL/SQL functions that support remote