Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ora-22922 when using clob in user defined table

hcuijpersMar 21 2024 — edited Mar 21 2024

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    
This post has been answered by Solomon Yakobson on Mar 22 2024
Jump to Answer
Comments
Post Details
Added on Mar 21 2024
24 comments
768 views