SQL> 
SQL> create type test_type as object (c1 number, c2 varchar2(10));
  2  /

Type created.

SQL> -- Can not bind arrays with execute immediate
SQL> declare
  2  type t1_row_type is table of test_type;
  3  x t1_row_type;
  4  begin
  5  execute immediate 'select test_type(c1,c2) from '||'t1' bulk collect into x;
  6  dbms_output.put_line(x(1).c1||' '||x(1).c2);
  7  forall i in 1..x.count
  8    execute immediate 'insert into '||'t2 '||'values :a' using x(i);
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-03001: unimplemented feature 
ORA-06512: at line 7 


SQL> drop type test_type;

Type dropped.

SQL> 
SQL> desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(10)

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(10)

SQL> select count(1) from t1;

  COUNT(1)                                                                      
----------                                                                      
         2                                                                      

SQL> select count(2) from t2;

  COUNT(2)                                                                      
----------                                                                      
         2                                                                      

SQL> -- Create global package variable to be passed into the dynamic SQL
SQL> create or replace package test_pkg as
  2  type t1_row_type is table of t1%RowType;
  3  t1_rows t1_row_type;
  4  end;
  5  /

Package created.

SQL> begin
  2  execute immediate 'select * from '||'t1' bulk collect into test_pkg.t1_rows;
  3  -- Array processing, trasformation here
  4  for i in 1..test_pkg.t1_rows.count loop
  5    test_pkg.t1_rows(i).c1:=test_pkg.t1_rows(i).c1+10;
  6    test_pkg.t1_rows(i).c2:=test_pkg.t1_rows(i).c2||'*';
  7  end loop;
  8  -- Note the use of forall insert with record type
  9  execute immediate 'begin '||
 10                    'forall i in 1..:a '||
 11                    'insert into '||'t2 '||'values test_pkg.t1_rows(i);'||
 12                    'end;' using test_pkg.t1_rows.count;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select count(2) from t2;

  COUNT(2)                                                                      
----------                                                                      
         4                                                                      

SQL> rollback;

Rollback complete.

SQL> drop package test_pkg;

Package dropped.

SQL> spool off