Posted by 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
Learning is not a spectator sport 
sorry, not getting you on this
“The BULK COLLECT into statement cannot be used repeatedly to append results into a table.
Instead, it silently truncates the target table each time. “
Could you please elaborate or show a demo/testcase ?
If you do:
select … bulk collect into my_array;
and then do
select … bulk collect into my_array;
then the contents from the first select are erased.
Thanks Cornnor , It was very interesting to know
Really this is the nice explain.. Thank you
Hi Norm,
There is a good article that I’ve referenced a few times on the Oracle blog, written by Steve Feuerstein that walks through BULK COLLECT and FORALL: https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall. In doing some refresher exercises recently I noticed in contained the truncate error with BULK COLLECT you mention (see Code Listing 6). Using your tip I rewrote it and got it to execute properly in the HR schema:
DECLARE
c_limit PLS_INTEGER := 10;
CURSOR employees_cur
IS
SELECT employee_id
FROM employees
WHERE department_id = 80;
TYPE employee_ids_t IS TABLE OF
employees.employee_id%TYPE;
l_employee_ids employee_ids_t;
l_employee_ids_agg employee_ids_t := employee_ids_t();
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employee_ids
LIMIT c_limit;
l_employee_ids_agg := l_employee_ids_agg MULTISET UNION ALL l_employee_ids;
EXIT WHEN employees_cur%NOTFOUND;
END LOOP;
END;
/