I created a (record and a) table type for internal use in a package. The record type contains a clob. When “reading”/using the clob for the second time i receive a ora-22922
I created the example below. This is the smallest i can create which reproduces my situation.
Please help because i'm completely clueless.
Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Error:
Error report -
ORA-22922: Niet-bestaande LOB-waarde.
ORA-06512: in "AA_TAB_TEST", regel 23
ORA-06512: in "AA_TAB_TEST", regel 23
ORA-06512: in regel 1
22922. 00000 - "nonexistent LOB value"
*Cause: The LOB value associated with the input locator does not exist.
The information in the locator does not refer to an existing LOB.
*Action: Repopulate the locator by issuing a select statement and retry
the operation.
create or replace package aa_tab_test is
type t_tabrec is record ( some_text clob );
type t_table is table of t_tabrec;
procedure testt;
end aa_tab_test;
/
create or replace package body aa_tab_test is
function vul_c_tab return t_table is
l_rec t_tabrec;
l_tab t_table := t_table();
begin
for i in 1..10 loop
l_rec.some_text := 'yada yada yada';
l_tab.extend(1);
l_tab(l_tab.count) := l_rec;
end loop;
return l_tab;
end vul_c_tab;
procedure testt is
l_tab t_table;
begin
l_tab := vul_c_tab;
for j in 1..5 loop
dbms_output.put_line('loop : '||j);
for rr in ( select * from table(l_tab)) loop
dbms_output.put_line(rr.some_text);
end loop;
end loop;
end testt;
end aa_tab_test;
/
exec aa_tab_test.testt