SQL> SQL> -- With 9.2.0.1, you can bulk operate on table of records now. SQL> SQL> drop table test2_a; drop table test2_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table test2_a as select * from test2 whre 1=2; create table test2_a as select * from test2 whre 1=2 * ERROR at line 1: ORA-00933: SQL command not properly ended SQL> SQL> select count(1) from test1 where c1 in ('Q','G'); COUNT(1) ---------- 0 SQL> -- Collection of records with FORALL SQL> -- Note that it is "values x(i)" not "values(x(i))" SQL> declare 2 type rec_type is table of test1%rowtype index by binary_integer; 3 x rec_type; 4 begin 5 select 'Q',0,0 into x(1) from dual; 6 select 'G',7,7 into x(2) from dual; 7 forall i in x.first..x.last 8 insert into test1 values x(i); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select count(1) from test1 where c1 in ('Q','G'); COUNT(1) ---------- 2 SQL> rollback; Rollback complete. SQL> SQL> -- Beware of the syntax put the "from table" at the end. SQL> -- You will not get any user-friendly error messages. SQL> declare 2 type rec_type is table of test1%rowtype index by binary_integer; 3 x rec_type; 4 begin 5 select * BULK COLLECT INTO x from test1; 6 end; 7 / PL/SQL procedure successfully completed. SQL> declare 2 type rec_type is record (c1 varchar2(2), c2 number(1)); 3 type tab_rec_type is table of rec_type index by binary_integer; 4 x tab_rec_type; 5 begin 6 select * BULK COLLECT INTO x from test2; 7 forall i in x.first..x.last 8 insert into test2_a values x(i); 9 end; 10 / insert into test2_a values x(i); * ERROR at line 8: ORA-06550: line 8, column 15: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 8, column 3: PL/SQL: SQL Statement ignored SQL> SQL> -- BULK COLLECT into SQL (table of object) type SQL> create or replace type test2_type as object (c1 varchar2(2), c2 number(1)); 2 / create or replace type test2_type as object (c1 varchar2(2), c2 number(1)); * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents SQL> create or replace type tab_test2_type as table of test2_type; 2 / Type created. SQL> set serveroutput on SQL> declare 2 x tab_test2_type; 3 begin 4 select test2_type(c1,c2) BULK COLLECT into x from test2; 5 for i in 1..x.count loop 6 dbms_output.put_line(x(i).c1||' '||x(i).c2); 7 end loop; 8 end; 9 / b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 PL/SQL procedure successfully completed. SQL> SQL> drop table test2_a; drop table test2_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop type tab_test2_type; Type dropped. SQL> drop type test2_type; Type dropped. SQL> SQL> spool offspool d:\yang\oracle\ObjectCollections\BulkAndRecords.txt SP2-0333: Illegal spool file name: "offspool d:\yang\oracle\ObjectCollections\BulkAndRecords.txt" (bad character: ' ') SQL> SQL> -- With 9.2.0.1, you can bulk operate on table of records now. SQL> SQL> drop table test2_a; drop table test2_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table test2_a as select * from test2 where 1=2; Table created. SQL> SQL> select count(1) from test1 where c1 in ('Q','G'); COUNT(1) ---------- 0 SQL> -- Collection of records with FORALL SQL> -- Note that it is "values x(i)" not "values(x(i))" SQL> declare 2 type rec_type is table of test1%rowtype index by binary_integer; 3 x rec_type; 4 begin 5 select 'Q',0,0 into x(1) from dual; 6 select 'G',7,7 into x(2) from dual; 7 forall i in x.first..x.last 8 insert into test1 values x(i); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select count(1) from test1 where c1 in ('Q','G'); COUNT(1) ---------- 2 SQL> rollback; Rollback complete. SQL> SQL> -- Beware of the syntax put the "from table" at the end. SQL> -- You will not get any user-friendly error messages. SQL> declare 2 type rec_type is table of test1%rowtype index by binary_integer; 3 x rec_type; 4 begin 5 select * BULK COLLECT INTO x from test1; 6 end; 7 / PL/SQL procedure successfully completed. SQL> declare 2 type rec_type is record (c1 varchar2(2), c2 number(1)); 3 type tab_rec_type is table of rec_type index by binary_integer; 4 x tab_rec_type; 5 begin 6 select * BULK COLLECT INTO x from test2; 7 forall i in x.first..x.last 8 insert into test2_a values x(i); 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> -- BULK COLLECT into SQL (table of object) type SQL> create or replace type test2_type as object (c1 varchar2(2), c2 number(1)); 2 / Type created. SQL> create or replace type tab_test2_type as table of test2_type; 2 / Type created. SQL> set serveroutput on SQL> declare 2 x tab_test2_type; 3 begin 4 select test2_type(c1,c2) BULK COLLECT into x from test2; 5 for i in 1..x.count loop 6 dbms_output.put_line(x(i).c1||' '||x(i).c2); 7 end loop; 8 end; 9 / b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 PL/SQL procedure successfully completed. SQL> SQL> drop table test2_a; Table dropped. SQL> drop type tab_test2_type; Type dropped. SQL> drop type test2_type; Type dropped. SQL> SQL> spool off