I had an observation come to me last week about PL/SQL and populating nested tables.
“The BULK COLLECT into statement cannot be used repeatedly to append results into a table.
Instead, it silently truncates the target table each time. “
This is true. However, if you need to use BULK COLLECT to append results into a single nested table data structure, it is trivial to come up with a workaround using the MULTISET syntax.
SQL> drop table t1 purge; Table dropped. SQL> create table t1 as select * from dba_objects; Table created. SQL> SQL> drop table t2 purge; Table dropped. SQL> create table t2 as select * from dba_objects; Table created. SQL> SQL> set serverout on SQL> declare 2 type ntt is table of t1%rowtype; 3 r1 ntt := ntt(); 4 r2 ntt := ntt(); 5 begin 6 select * bulk collect into r1 from t1 ; 7 select * bulk collect into r2 from t2 ; 8 dbms_output.put_line('T1 count= '||r1.count); 9 dbms_output.put_line('T2 count= '||r2.count); 10 11 r1 := r1 MULTISET UNION ALL r2; 12 13 dbms_output.put_line('TOTAL = '||r1.count); 14 end; 15 / T1 count= 99250 T2 count= 99250 TOTAL = 198500 PL/SQL procedure successfully completed.
Read more about the multiset syntax here