As of 12, we can use the TABLE operator with package-defined associative arrays, which is nice. But I don't understand why this doesn't work (in 12cR2):
create table t1 ( id number );
create package pt1 as
type t1row is record (
id number
);
type t1row_tt is table of t1row index by pls_integer;
end;
/
declare
r pt1.t1row_tt;
begin
r(1).id := 1;
insert into t1 (id) select id from table(r);
end;
/
That gives ORA-00902: invalid datatype. However this works:
declare
r pt1.t1row_tt;
begin
r(1).id := 1;
for cur in ( select * from table(r)) loop
insert into t1 (id) values (cur.id);
end loop;
end;
Is it because the first case passes the table() call off to the SQL engine and it doesn't work there?