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;
/
