Hello AskTom Team,
I am using TABLE operator on locally defined nested table type.
As per my understanding of 12c plsql manual, it is supported
I am doing following:
- Defined a type in package specification "type t1_tblType is TABLE of r1_recType;"
- In procedure of the package body, I populate a nested table variable (t1_tbl) of same type and then try to use that in following manner
---- Using cursor for loop , where I am selecting from this nested table variable using table operator, it is working fine and printing its content
---- When I used same nested variable in "insert into....select from TABLE(nested table variable)" , it does not work. I have tried 2-3 variations of insert statement - for all of them, it returns - "ORA-00902: invalid datatype"
Following are variation I have tried
insert into t3 select * from TABLE(t1_tbl);
insert into t3 select id1,a1 from TABLE(t1_tbl);
insert into t3 (id1,a1) select id1,a1 from TABLE(t1_tbl);
So, it seems you can select from nested table using TABLE operator. But, in INSERT - it does not seem to be supported.
Please let me know if my understanding is correct or please suggest, if there is any mistake I am making and how to correct it (I understand, I cna use forall statement...but is there any other way, especially as I have mentioned above using TABLE operator)
I am attaching entire code snippet below (DDL statement for table t3 is listed in commented form at beginning, please run it separately, in case you want to try it at your end)
/*
create table t3
(
id1 number(18),
a1 varchar2(30)
);
*/
create or replace package test_pkg02
as
type r1_recType is RECORD
(
id1 number(18),
a1 varchar2(30)
);
type t1_tblType is TABLE of r1_recType;-- INDEX BY PLS_INTEGER;
procedure p1;
end test_pkg02;
/
show errors;
create or replace package body test_pkg02
as
procedure p1 is
r1_rec r1_recType;
t1_tbl t1_tblType;
i pls_integer;
begin
t1_tbl := t1_tblType();
r1_rec.id1 := 101;
r1_rec.a1 := 'a101';
t1_tbl.EXTEND;
t1_tbl(1) := r1_rec;
r1_rec.id1 := 102;
r1_rec.a1 := 'a102';
t1_tbl.EXTEND;
t1_tbl(2) := r1_rec;
r1_rec.id1 := 103;
r1_rec.a1 := 'a103';
t1_tbl.EXTEND;
t1_tbl(3) := r1_rec;
for cur_rec in ( select * from TABLE(t1_tbl) b )
loop
dbms_output.put_line('cur_rec.id1='|| cur_rec.id1);
end loop;
insert into t3 select * from TABLE(t1_tbl);
--insert into t3 select id1,a1 from TABLE(t1_tbl);
-- insert into t3 (id1,a1) select id1,a1 from TABLE(t1_tbl);
end p1;
end test_pkg02;
/
show errors;
set serveroutput on size 100000;
execute test_pkg02.p1;