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?