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.