CODEWITHSUNDEEP
sqloracle-databaseclob
superigno
superigno

Reputation: 1024

Reusing CLOB in Oracle

If I'm gonna reuse my CLOB variable, will reinitializing it to a new value suffice or do I need to use freetemporary function? Do you have any other way (best way) to do this?

DECLARE
   message CLOB;
BEGIN

   message := 'The quick brown fox jumps over the lazy dog.';

   dbms_output.put_line(message);

   message := 'Test';

   dbms_output.put_line(message);

END;

OR

DECLARE
    message CLOB;
 BEGIN

    message := 'The quick brown fox jumps over the lazy dog.';

    dbms_output.put_line(message);

    dbms_lob.freetemporary(message);

    message := 'Test';

    dbms_output.put_line(message);

 END;

Upvotes: 1

Views: 1800

Answers (1)

MT0
MT0

Reputation: 167982

Your code is fine (top and bottom version).

See the Oracle Documentation: PL/SQL Semantics for LOBs:

CLOB Variables in PL/SQL

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 will raise an error because myStory is 
15 -- longer than 100 bytes
16 myGist := myStory;
17 END;

Please note that in line 10 of "CLOB Variables in PL/SQL", a temporary CLOB is implicitly created and is pointed to by the revisedStory CLOB locator. In the current interface the line can be expanded as:

buffer VARCHAR2(32000)
DBMS_LOB.CREATETEMPORARY(revisedStory);
buffer := UPPER(DBMS_LOB.SUBSTR(myStory,100,1));
DBMS_LOB.WRITE(revisedStory,length(buffer),1, buffer);

In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:

DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));

So you don't need to call the DBMS_LOB procedures as PL/SQL will implicitly convert your code to the appropriate DBMS_LOB calls.

You also do not need to free the values as:

Temporary LOBs created in a program block as a result of a SELECT or an assignment are freed automatically at the end of the PL/SQL block/function/procedure. You can choose to free the temporary LOBs to reclaim system resources and temporary tablespace by calling DBMS_LOB.FREETEMPORARY on the CLOB variable.

But you can choose to manually call this if you want to control when the memory is freed (as you do in the bottom version).

Upvotes: 1

Related Questions