It looks like you're leaking both pointers and data.
There's one place in your code that is the "obvious guess" for the problem:
res := substr(res,2)
Based on previous (old) experience I'd try introducing a second lob here and
doing:
res2 := substr(res,2)
return res2;
I'd then do something similar with the two places where you've got
res := res || tmp
Something like:
res2 := res || tmp;
res := res2;
Essentially avoid assigning the clob to itself.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Sayan Malakshinov [xt.and.r@xxxxxxxxx]
Sent: 24 February 2016 21:28
To: Stefan Koehler
Cc: ORACLE-L
Subject: Re: v$process_memory_detail: Category=Other, HEAP_NAME = kolaslAssign
Hi Stefan,
Thanks for confirming my guess!
Unfortunately, this issue is on windows server. And I can't to create a test
case that will have the same behaviour like buggy query, because my test with
the same function shows that memory is freeing normally.
Source code is very simple:
create type t_objstrset as table of varchar2(4000);
/
create or replace function func_collection_to_clob (
p_collection t_objstrset
,p_delim varchar2:=','
)
return clob
as
res clob;
tmp varchar2(32767);
elm varchar2(4000);
begin
if p_collection is null then
res:=null;
else
for i in 1..p_collection.count loop
elm:=p_collection(i);
if length(tmp)+length(elm)>=32767 then
res:=res||tmp;
tmp:=null;
end if;
tmp:=tmp||p_delim||elm;
end loop;
res:=res||tmp;
end if;
return substr(res,2);
end;
/
I've tried also to create lobs with dbms_lob.createtemporary(res ,true,
dbms_lob.call);
But without success:
create or replace function func_collection_to_clob (
p_collection t_objstrset
,p_delim varchar2:=','
)
return clob
as
res clob;
tmp varchar2(32767);
elm varchar2(4000);
begin
if p_collection is null then
res:=null;
else
dbms_lob.createtemporary(res ,true, dbms_lob.call);
for i in 1..p_collection.count loop
elm:=p_collection(i);
if length(tmp)+length(elm)>=32767 then
res:=res||tmp;
tmp:=null;
end if;
tmp:=tmp||p_delim||elm;
end loop;
res:=res||tmp;
end if;
res:=substr(res,2);
return res;
end;
/
