CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Tatiana
Tatiana

Reputation: 1499

SELECT FROM user-defined table type, ORA-22813

I have really interesting problem.

I have user-defined table type

create or replace TYPE "TABLE_OF_NUMBER" AS TABLE OF NUMBER

I have table with millions of rows - calls

I have pl/sql block which works okay

declare
  v_calls     TABLE_OF_NUMBER;
begin
  select id bulk collect
  into v_calls
  from calls;
end;

I have another pl/sql block which gives me an error

declare
  v_calls     TABLE_OF_NUMBER;
  t1 number;
begin
  select id bulk collect
  into v_calls
  from calls;

  select count(*) into t1 from table(v_calls);
end;

ORA-22813: Operand value exceeds system limits.

And I found an explanation of error:

Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory.

My actual goal is to get this:

declare
  v_calls     TABLE_OF_NUMBER;
  v_events TABLE_OF_NUMBER;

begin

  select id bulk collect
  into v_calls
  from calls;

  select id bulk collect
  into v_events 
  from events
  where call_id in (select * from table(v_calls));

end;

But I already find out that problem command is select * from table(v_calls)

I didn't find anything about restrictions for TABLE(user_defind_table) command.

Does anyone know what is the problem and how can I avoid it?

Upvotes: 3

Views: 1856

Answers (2)

Tatiana
Tatiana

Reputation: 1499

The restriction for select * from table(v_calls) could be found here

I find few solutions:

  1. As @Stawros said - use bulk collect ... limit In my case performance degrades dramatically, so it isn't solution for me

  2. Use temporary tables instead of user defined tables. It helps avoid error, but performance still really poor

  3. Change whole code (logic) - unfortunately, that is my way.

Upvotes: 1

Stawros
Stawros

Reputation: 935

Possible solution:

declare
  v_calls     TABLE_OF_NUMBER;
  v_events TABLE_OF_NUMBER;
  v_events_tmp TABLE_OF_NUMBER;
  cursor cur_calls is
  select id
  from calls;
begin
  open cur_calls;
    loop
       fetch cur_calls 
        bulk collect 
        into v_calls 
       limit 1000;
       exit when v_calls.count = 0;

      select id bulk collect
      into v_events_tmp 
      from events
      where call_id in (select * from table(v_calls));

      v_events := v_events_tmp MULTISET UNION v_events;

    end loop;
  close cur_calls;
end;

Upvotes: 3

Related Questions