Bulk collect on records

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.