This helps lots with a similar problem I'm having. I still wonder, though, about CLOBs in package methods:

. Are CLOBS declared within methods session-length unless explicitly released?

. Could I use a particular package-level CLOB as a NOCOPY parameter to access it within a method?

A problem I have is that I must call a function more than once in one statement (with different parameters) to build a single CLOB result field. This makes using a package-level CLOB field difficult. Something like this:

Package:
...
---- Internal function -------------
Function buildstring(param in Number)
Return CLOB
is
tmpCLOB CLOB;
Begin
Select bigstring
Into tmpCLOB
From myTable
where keyval = param

Return tmpCLOB;

End buildstring;

----- Public proc -----------------
Procedure Make_entry(param1,param2)
is
Begin
Insert Into NewTable
BigString
Values(
buildstring(param1)||
' - and - '||
buildstring(param2));

End Make_entry;

End;
----------------------------------------------------

In this scenario, when Make_Entry is called a number of times in a session, I believe the v$temporary_lobs goes quite high. I think because the tmpCLOB in the function doesn't go away?

Is there a way to fix this, or to be sure to release a locally-scoped CLOB when it is returned from a function like this?

Thanks for the help.
Tim

Thanks,

How should I clean up a temporary CLOB created by a function like 'Internal_Function' in a situation like this:
Package:
------------------------------------
...
pkglobTemp CLOB;

Function Internal_Function(param)
is
tmpCLOB CLOB;
begin
Select clobField
Into tmpCLOB
From myTable
Where keyField = param;

Return tmpCLOB;

End Internal_Function;
------------------------------------
Procedure publicProc(param1)
is
For myRecs in
(Select Field1, Field2
From mySource_Tbl
Where keyField = param1)
Loop
pkglobTemp := pkglobTemp ||
Internal_Function(myRecs.Field1)||
' - and - '
Internal_Function(myRecs.Field2);
End Loop;

INSERT INTO myLog_tbl FieldBig Values(pkglobTemp);

End publicProc;

End;
------------------------------------------------------

Does the function get a new CLOB reference to a new CLOB value for each call? If so, then how do I 'clean up' these temp CLOBS?

The light goes on! Maybe...

After some testing & verification of what happens I
now do this:

------------------------------------
...

pkglobTemp1 CLOB;
pkglobTemp2 CLOB;
pkglobTemp3 CLOB;

pkglobReturnValue CLOB

Procedure Internal_Procedure(param)
is
begin
Select clobField
Into pkglobReturnValue
From myTable
Where keyField = param;

End Internal_Procedure;
------------------------------------
Procedure publicProc(param1)
is
lstrTemp varchar2(32000);
Begin

-- Empty contents
dbms_lob.trim(pkglobTemp,0);

For myRecs in
(Select Field1, Field2
From mySource_Tbl
Where keyField = param1)
Loop
-- Get first gob
Internal_procedure(myrecs.Field_1);

-- Add it to total
dbms_lob.append(pkglobTemp,pkglobReturnValue);

-- Make string value to add
lstrTemp := ' - and this value: ';

-- Add it
dbms_lob.WriteAppend(pkglobTemp,length(lstrTemp),lstrTemp);

-- Get next gob
Internal_procedure(myrecs.Field_2);

-- Add to total
dbms_lob.append(pkglobTemp,pkglobReturnValue);

End Loop;

-- Write it
INSERT INTO myLog_tbl FieldBig Values(pkglobTemp);

End publicProc;

Begin

dbms_lob.Createtemporary(pkglobTemp1);
dbms_lob.Createtemporary(pkglobTemp2);
dbms_lob.Createtemporary(pkglobTemp3);
dbms_lob.Createtemporary(pkglobReturnValue);

End;
------------------------------------------------------

This seems to *not* build mucho lost LOBs - like I was getting before. I would appreciate any comments on if this method really sucks or not.

I changed the internal function to a procedure because it was returning a package-global variable anyhow. I can't seem to get used to functions returning these things - like I've seen in some examples.

Thanks again,
Tim

<quote>CLOBS are tiny. What they POINT TO is large. NOCOPY on a clob is funny (i wish our docs wouldn't do that). It is not like they are big.</quote>

According to the doco ... when using temporary LOBs, it does make sense to use NOCOPY:

<quote>
The PL/SQL compiler makes temporary copies of actual arguments bound to OUT or IN OUT parameters. If the actual parameter is a temporary LOB, then the temporary copy is a deep (value) copy.

The following PL/SQL block illustrates the case where the user incurs a deep copy by passing a temporary LOB as an IN OUT parameter.

DECLARE
a blob;
procedure foo(parm IN OUT blob) is
BEGIN
...
END;
BEGIN
dbms_lob.createtemporary(a, TRUE);
-- the following call results in a deep copy of the blob a
foo(a);
END;

To minimize deep copies on PL/SQL parameter passing, use the NOCOPY compiler hint where possible.
</quote>

Unless of course I am misreading the whole thing ...

Hi Tom,

I have a requirement where I need to create a temporary lob and initialize it to a fixed length value like

first 5 bytes shud be by default something like '6A6B500010'
and then the rest 10 bytes shud be all 0s. what is the easiest way to initialize the blob?

Thanks.

I have a xml program that recursively traverses a tree structure in the db. I used clobs to store the xml as it was being generated. Long story short our oracle process jumped from 147mb to 1.5gb of RAM. I did some testing with clobs and recursion and this is what I found. I used the following package to test:

---------------------------------------------------------------------------------------------
create or replace package recursion_benchmark is
pv_clob CLOB;
pv_temp_clob CLOB := '123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789-'||chr(13);

PROCEDURE loop_clobs(v_num_clobs IN NUMBER);
PROCEDURE loop_numbers(v_num_clobs IN NUMBER);
PROCEDURE recursion_clob(p_start IN NUMBER, p_limit IN NUMBER, p_clob IN OUT NOCOPY CLOB);
PROCEDURE recursion_num(p_start IN NUMBER, p_limit IN NUMBER, p_num IN OUT NUMBER);
PROCEDURE recursion_varchar2(p_start IN NUMBER, p_limit IN NUMBER, p_varchar2 IN OUT NOCOPY CLOB);
PROCEDURE recursion_pub_clob(p_start IN NUMBER, p_limit IN NUMBER);
PROCEDURE recursion_pri_clob(p_limit IN NUMBER);
PROCEDURE recursion_clob_array(p_limit IN NUMBER);
PROCEDURE recursion_clob_array_append(p_limit IN NUMBER);
PROCEDURE recursion_pri_dbms_clob(p_limit IN NUMBER);
PROCEDURE recursion_dbms_clob(p_limit IN NUMBER);
PROCEDURE recursion_dbms_clob_param(p_limit IN NUMBER);
PROCEDURE recursion_just_declare(p_start IN NUMBER, p_limit IN NUMBER);
END recursion_benchmark;

create or replace package body recursion_benchmark IS
TYPE t_clob_array IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
C_APPEND_STRING VARCHAR2(100) := '123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789-'||chr(13);
lv_clob_array t_clob_array;
lv_num_clobs NUMBER;
lv_clob CLOB;
lv_dbms_clob CLOB;
lv_dbms_clob_append CLOB;

PROCEDURE loop_clobs(v_num_clobs IN NUMBER)
IS
TYPE t_clob_ar IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
v_arr t_clob_ar;
v_main CLOB;
BEGIN
FOR i IN 1..v_num_clobs LOOP
v_arr(i) := C_APPEND_STRING;
v_main := v_main||v_arr(i);
END LOOP;
dbms_output.put_line(dbms_lob.getlength(v_main));
END;
PROCEDURE loop_numbers(v_num_clobs IN NUMBER)
IS
TYPE t_num_ar IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_arr t_num_ar;
v_main NUMBER := 0;
BEGIN
FOR i IN 1..v_num_clobs LOOP
v_arr(i) := 1;
v_main := v_main + v_arr(i);
END LOOP;
END;

PROCEDURE recursion_clob(p_start IN NUMBER, p_limit IN NUMBER, p_clob IN OUT NOCOPY CLOB)
is
begin
p_clob := p_clob||C_APPEND_STRING;
IF(p_start <= p_limit)THEN
recursion_clob(p_start + 1, p_limit, p_clob);
END IF;
end recursion_clob;

PROCEDURE recursion_num(p_start IN NUMBER, p_limit IN NUMBER, p_num IN OUT NUMBER)
is
begin
p_num := p_num + 1;
IF(p_start <= p_limit)THEN
recursion_num(p_start + 1, p_limit, p_num);
END IF;
end recursion_num;

PROCEDURE recursion_varchar2(p_start IN NUMBER, p_limit IN NUMBER, p_varchar2 IN OUT NOCOPY CLOB)
is
begin
p_varchar2 := p_varchar2||'123'||chr(13);
IF(p_start <= p_limit)THEN
recursion_varchar2(p_start + 1, p_limit, p_varchar2);
END IF;
end recursion_varchar2;

PROCEDURE recursion_pub_clob(p_start IN NUMBER, p_limit IN NUMBER)
is
BEGIN
pv_clob := pv_clob||C_APPEND_STRING;
IF(p_start <= p_limit)THEN
recursion_pub_clob(p_start + 1, p_limit);
END IF;
end recursion_pub_clob;

PROCEDURE recursion_pri_clob(p_start IN NUMBER, p_limit IN NUMBER)
is
BEGIN
lv_clob := lv_clob||C_APPEND_STRING;
IF(p_start <= p_limit)THEN
recursion_pri_clob(p_start + 1, p_limit);
END IF;
end recursion_pri_clob;

PROCEDURE recursion_pri_clob(p_limit IN NUMBER)
IS
BEGIN
lv_clob := empty_clob();
recursion_pri_clob(1, p_limit);
dbms_output.put_line('clob length = '||dbms_lob.getlength(lv_clob));
END;

PROCEDURE recursion_clob_array(p_start IN NUMBER, p_limit IN NUMBER)
IS
BEGIN
lv_num_clobs := lv_num_clobs + 1;
lv_clob_array(lv_num_clobs) := C_APPEND_STRING;
IF(p_start <= p_limit)THEN
recursion_clob_array(p_start + 1, p_limit);
END IF;
END;

PROCEDURE recursion_clob_array(p_limit IN NUMBER)
IS
BEGIN
lv_num_clobs := 0;
recursion_clob_array(1, p_limit);

END;

PROCEDURE recursion_clob_array_append(p_limit IN NUMBER)
IS
i NUMBER;
BEGIN
lv_clob := empty_clob();
recursion_clob_array(p_limit);
lv_clob := lv_clob_array(1);
FOR i IN 2..lv_num_clobs-1 LOOP
lv_clob := lv_clob||lv_clob_array(i);
END LOOP;
dbms_output.put_line('clob length = '||length(lv_clob));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('i = '||i);
END;

PROCEDURE recursion_pri_dbms_clob(p_start IN NUMBER, p_limit IN NUMBER)
IS
BEGIN
dbms_lob.append(lv_dbms_clob, lv_dbms_clob_append);
IF(p_start <= p_limit)THEN
recursion_pri_dbms_clob(p_start + 1, p_limit);
END IF;
END;

PROCEDURE recursion_pri_dbms_clob(p_limit IN NUMBER)
IS
BEGIN
dbms_lob.createtemporary(lv_dbms_clob, FALSE);
dbms_lob.createtemporary(lv_dbms_clob_append, FALSE);
dbms_lob.write(lv_dbms_clob_append, length(C_APPEND_STRING), 1, C_APPEND_STRING);
recursion_pri_dbms_clob(1, p_limit);
dbms_output.put_line('length = '||dbms_lob.getlength(lv_dbms_clob));
dbms_lob.freetemporary(lv_dbms_clob);
dbms_lob.freetemporary(lv_dbms_clob_append);
END;

FUNCTION recursion_dbms_clob(p_start IN NUMBER, p_limit IN NUMBER)RETURN CLOB
IS
v_to_return CLOB;
v_temp CLOB;
BEGIN
dbms_lob.createtemporary(v_to_return, FALSE);
dbms_lob.write(v_to_return, length(C_APPEND_STRING), 1, C_APPEND_STRING);
IF(p_start <= p_limit)THEN
v_temp := recursion_dbms_clob(p_start + 1, p_limit);
dbms_lob.append(v_to_return, v_temp);
dbms_lob.freetemporary(v_temp);
END IF;
RETURN v_to_return;
END;

PROCEDURE recursion_dbms_clob(p_limit IN NUMBER)
IS
v_clob CLOB;
BEGIN
v_clob := recursion_dbms_clob(1, p_limit);
dbms_output.put_line('length = '||dbms_lob.getlength(v_clob));
dbms_lob.freetemporary(v_clob);
END;

PROCEDURE recursion_dbms_clob_param(p_start IN NUMBER, p_limit IN NUMBER, p_clob IN OUT NOCOPY CLOB)
IS
BEGIN
IF(p_start <= p_limit)THEN
dbms_lob.append(p_clob, lv_dbms_clob_append);
recursion_dbms_clob_param(p_start + 1, p_limit, p_clob);
END IF;
END;

PROCEDURE recursion_dbms_clob_param(p_limit IN NUMBER)
IS
v_clob CLOB;
BEGIN
dbms_lob.createtemporary(v_clob, FALSE);
dbms_lob.createtemporary(lv_dbms_clob_append, FALSE);
dbms_lob.write(lv_dbms_clob_append, length(C_APPEND_STRING), 1, C_APPEND_STRING);
recursion_dbms_clob_param(1, p_limit, v_clob);
dbms_output.put_line('length = '||dbms_lob.getlength(v_clob));
dbms_lob.freetemporary(v_clob);
dbms_lob.freetemporary(lv_dbms_clob_append);
END;

PROCEDURE recursion_just_declare(p_start IN NUMBER, p_limit IN NUMBER)
IS
v_clob CLOB := empty_clob();
BEGIN
IF(p_start <= p_limit)THEN
recursion_just_declare(p_start + 1, p_limit);
END IF;
END;
end recursion_benchmark;
---------------------------------------------------------------------------------------------




Here are the results that I found most interesting:

program iter- memory time
ations used
--
loop_num 10000 376 0.031
recursion_pri_dbms_clob 4000 1484 6.922
recursion_dbms_clob_param 4000 1524 6.781
recursion_num 4000 1692 -1
recursion_dbms_clob_param 10000 2920 17.735
recursion_num 10000 3552 0.062
recursion_num 10000 3580 0.062
loop_clob 10000 8944 455.781
recursion_varchar2 4000 11560 0.156
recursion_dbms_clob 4000 13672 90.921
recursion_clob_array 4000 14180 7.719
recursion_clob_array_append 4000 15436 71.265
recursino_pub_clob 4000 17352 166.375
recursion_clob 4000 17788 104.922
recursion_pri_clob 4000 18452 127.484
recursion_dbms_clob 10000 33192 749.922
recursion_clob_array_append 10000 37772 436.562
recursion_clob 10000 43752 1037.469
recursion_just_declare 10000 28280 .781

1
Why is it that recursion_clob for 4000 iterations takes up almost twice the memory as loop_clob for 10,000 iterations?

2
How is it that recursion_pri_clob and recursion_pub_clob used almost the same amount of memory as recursion_clob? both pub and pri use a single clob that was declared in the package spec or body. And why does recursion_dbms_clob_param use so little memory and run so fast?

3
I'm guessing that there is some crazy overhead when using || instead of dbms_lob.append. But if that's the case then why make clobs so easy to work with in 9i if they are just going to be memory hogs? I base this off the fact that recursion_dbms_clob_param takes so little memory and runs so fast.

4
I've also noticed that concating to the end of a clob seems to be pretty processor intensive. Is there a reason that it's slower than dbms_lob.append?

5
Why can i use dbms_lob.append on a clob that has been set to null, but not set to empty_clob(invalid lob locator error)?

6
Lastly how much memory gets allocated when a clob is declared? recursion_just_declare seems to use up an excess of memory for never assigning a value. When exactly does a clob in 9i start pointing to stuff? seems if you just declare it, it wouldn't be pointing at anything and should take almost no space in memory.

Hello Tom,

i wrote a little application to display auditing data to the webbrowser that works nicely so far.

Since sometimes the data grows quite massive over time, i figured instead of sending 400'000 lines of text to the browser, i'd bulk collect it, load it into a temporary lob and send it to the client as a file.

The procedure i'm using to do this:

PROCEDURE show_os_details (db IN VARCHAR2, name IN VARCHAR2)
IS
TYPE MYCURS IS REF CURSOR;
TYPE t_cmt IS TABLE OF VARCHAR2(4000);
c MYCURS;
cmt t_cmt;
stmt VARCHAR2(1000);
obj BLOB;
buf RAW(8000);
BEGIN
html_begin;
htp.p('Showing OS / connection details for user ' || name || '<BR>');

IF (name != '**unknown**') THEN
stmt := 'SELECT DISTINCT comment_text FROM ' ||db || ' WHERE lower(os_username) = lower( :name )';
OPEN c FOR stmt USING name;
ELSE
stmt := 'SELECT DISTINCT comment_text FROM ' ||db || ' WHERE os_username IS NULL';
OPEN c FOR stmt;
END IF;
FETCH c BULK COLLECT INTO cmt;

IF (cmt.LAST > 100) THEN
htp.p('More than 100 matches. Sending you a file in a moment...');

DBMS_LOB.CREATETEMPORARY(obj,false);
FOR r IN cmt.FIRST..cmt.LAST
LOOP
buf := UTL_RAW.CAST_TO_RAW(cmt(r));
DBMS_LOB.WRITEAPPEND(obj,UTL_RAW.LENGTH(buf),buf);
END LOOP;

htp.p('Content-Length: '||dbms_lob.getlength(obj));
htp.p('Content-Disposition: attachment; filename="requested-data.txt"');
owa_util.http_header_close();
wpg_docload.download_file(obj);
DBMS_LOB.FREETEMPORARY(obj);

RETURN;
END IF;

FOR r IN cmt.FIRST..cmt.LAST
LOOP
htp.p(cmt(r) || '<BR>');
END LOOP;
END;

When i attempt to execute it, it returns:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 782
ORA-06512: at "AUDIT_REPORT.AUDIT_REPORT", line 232

which is the very line:
DBMS_LOB.WRITEAPPEND(obj,UTL_RAW.LENGTH(buf),buf);

I would like to ask you now:

a) am i even on the right track with the approach i chose to code this or would you do this differently ?

b) i've several times ran into problems with functions that return a number and supplying that into another taht requires a "binary integer". is that what causes this error ?

Would be great to hear your input on this.

Regards Stefan

Hi Tom,

I have found that dbms_lob.freeTemporary doesn't free temporary BLOB in Oracle 9. Metalink suggests only one workaround (228479.1): "disconnect the database user session." I can't use this suggestion, because session can't be disconnected. Is there other solution?

Thanks

Hi Tom,

I have tested and found that session has always only 1 extent in sort segment after use of all my temporary lobs (v$sort_usage). Does this mean that space is really freed after use of temporary lob and only 1 extent remains in use?

Thanks

Hi Tom,

Following has been taken from

Oracle Database - Application Developer's Guide - Large Objects
10g Release 2 (10.2) Part No. B14249-01

"In PL/SQL, C (OCI), Java and other programmatic interfaces, SQL query results or PL/SQL program executions return temporary LOBs for operation/function calls on LOBs. For example;

SELECT substr(CLOB_Column, 4001, 32000) FROM ....

If the query is executed in PL/SQL, then the returned temporary LOBs automatically get freed at the end of a PL/SQL program block. You can also explicitly free the temporary LOBs any time. in OCI and Java, the returned temporary LOB must be freed by the user explicitly.

Without proper deallocation of the temporary LOBs returned from SQL queries, temporary tablespace gets filled up steadily and you could observe performance degradation."

We have a third party application which is doing a batch processing which includes modifiations to the BLOB and XMLTYPE columns. Its not in PL/SQL.

1) How can we make sure that the application is freeing up the temporary LOBs?
2) If the application is not freeing then can we free it from the database using DBMS_LOB.FREETEMPORARY?

Thanks for answering

ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2>         type array is table of clob index by binary_integer;
ops$tkyte%ORA10GR2>         l_clob array;
ops$tkyte%ORA10GR2> end;
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  my_pkg.l_clob(i) := null;
  5                  dbms_lob.createTemporary(my_pkg.l_clob(i),TRUE);
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       148         10            0             0

ops$tkyte%ORA10GR2> !plus
/bin/bash: plus: command not found

ops$tkyte%ORA10GR2> !sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 25 13:33:10 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2>         type array is table of clob index by binary_integer;
ops$tkyte%ORA10GR2>         l_clob array;
ops$tkyte%ORA10GR2> end;
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  my_pkg.l_clob(i) := null;
  5                  dbms_lob.createTemporary(my_pkg.l_clob(i),TRUE);
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       148         10            0             0
       158         10            0             0

I have the following flow in our application

1. Java servlet calls plsql stored procedure using connection pool
2. PLSQL stored procedure creates temporary lob
3. PLSQL stored procedure builds xml document in lob
4. PLSQL stored procedure returns lob to java
5. Java streams lob back to client browser

In this case what is the correct way to ensure that the memory for the lobs is freed up. I cannot clean up in the plsql since java needs the lob to "still be there" so it can stream it. Equally since the sessions never close (conn pool) the memory is never cleaned up this way.

1. Set the lob duration to call
2. Explicitly call free temporary from java once done streaming

If the answer is 2, have you any idea how one calls freetemporary from java? Is it a case of binding in the lob to a call to dbms_lob.freetemporary using a prepared statement?

Thanks in advance

if I had a function which returned a temporary CLOB, how would I free that.

As an example;

create or replace function test_lob (p_in varchar2) return clob as
  l_out clob;
begin
  dbms_lob.createtemporary(l_out, false, dbms_lob.call);
  l_out := p_in || ' there';
  return l_clob;
end;
/
select test_lob('hello') from dual
/
select *
from v$temporary_lobs
/

Where would I do a "freetemporary"?

"the client application would free it, whatever fetched it is responsible for doing that"

Interesting. We have a stored procedure (SP) which is executed on a daily basis, just for example it's a bit like this;

create or replace procedure test as 
  l_out xmltype;
  l_some_number number := 1000;

  procedure do_something (p_clob clob) is
  begin
    null;
  end;
begin
  for i in 1..l_some_number
  loop
    select xmlelement(dummy)
    into l_out
    from dual;

    do_something(l_out.getclobval());  
  end loop;
end;
/

The above procedure in our environment appears to be "leaking memory". Suspecting getclobval() is the culprit, I would like to freetemporary after do_something, but not sure how.

ops$tkyte%ORA10GR2> create or replace function get_stat_val( p_name in varchar2 ) return number
  2  as
  3          l_val number;
  4  begin
  5      select b.value
  6            into l_val
  7        from v$statname a, v$mystat b
  8       where a.statistic# = b.statistic#
  9         and a.name = p_name;
 10
 11          return l_val;
 12  end;
 13  /

Function created.

ops$tkyte%ORA10GR2> create or replace procedure test as
  2    l_out xmltype;
  3    l_some_number number := 1000;
  4    l_pga number;
  5
  6    procedure do_something (p_clob clob) is
  7    begin
  8      null;
  9    end;
 10  begin
 11    for i in 1..l_some_number
 12    loop
 13      l_pga := get_stat_val( 'session pga memory' );
 14      select xmlelement(dummy)
 15      into l_out
 16      from dual;
 17
 18      do_something(l_out.getclobval());
 19      if ( (get_stat_val('session pga memory')-l_pga) <> 0 )
 20      then
 21          dbms_output.put_line
 22          ( i || ') pga before ' || l_pga || '.... ' ||
 23            (get_stat_val('session pga memory')-l_pga) || ' pga change' );
 24      end if;
 25    end loop;
 26  end;
 27  /

Procedure created.

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec test
1) pga before 855752.... 131072 pga change

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec test

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec test

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       277          0            0             0

ops$tkyte%ORA10GR2> exec dbms_lob.createtemporary( :xxx, true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       277          1            0             0

Hi Tom,
We are running oracle 10.2.0.5 .
We have an application that uses connection pool . It calls an oracle method that returns it a clob.
We are doing a freeTemporay prior to closing connection in the java application.

When I try to test this to make sure that the lob is freed :

SELECT * from gv$temporary_lobs gl ,
gv$session b
WHERE gl.SID=b.SID
AND gl.INST_ID=b.INST_ID

This returns me a row until I close out the session. The CACHE_LOBS column has a value of 0.
Does this mean that the memory is freed? Please advise.

thanks!