Lets start with a working example of bulk collect into records.

SQL> create table T ( c1 number, c2 number ); Table created. SQL> declare 2 type r is record ( 3 x number, 4 y number ); 5 6 type rt is table of r; 7 8 d rt; 9 10 begin 11 select rownum, rownum 12 bulk collect into d 13 from all_Objects 14 where rownum <= 20; 15 16 forall i in 1 .. 20 17 insert into T values d(i); 18 19 end; 20 / PL/SQL procedure successfully completed.

But what if table T has three columns, and we wanted to add the constant value "10" when we insert. Then we have problems because it would look like this:

SQL> declare 2 type r is record ( 3 x number, 4 y number ); 5 6 type rt is table of r; 7 8 d rt; 9 10 begin 11 select rownum, rownum 12 bulk collect into d 13 from all_Objects 14 where rownum <= 20; 15 16 forall i in 1 .. 20 17 insert into T values ( d(i).x, d(i).y, 10); 18 19 end; 20 / insert into T values ( d(i).x, d(i).y, 10); * ERROR at line 17: ORA-06550: line 17, column 28: PLS-00436: implementation restriction: cannot reference .... etc

However, what we CAN do is use objects and then apply SQL to them. All we need is some objects to mimic our PLSQL types

SQL> create or replace type r is object ( x number, y number ); 2 / Type created. SQL> create or replace type rt is table of r; 2 / Type created. SQL> declare 2 d rt; -- this is now pointing to a database definition not a plsql definition 3 4 begin 5 select r(rownum, rownum) 6 bulk collect into d 7 from all_Objects 8 where rownum <= 20; 9 10 insert into T 11 select x,y,10 12 from table(d); 13 14 end; 15 / PL/SQL procedure successfully completed.