Temporary LOBs Created by SQL and PL/SQL Built-in Functions

6.3 Temporary LOBs Created by SQL and PL/SQL Built-in Functions

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 CLOB is implicitly created and is pointed to by the revisedStory CLOB locator.
In line number 13, myGist is 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 because 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.