BULK COLLECT into nested table

October 24, 2016

1–2 minutes

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

7 responses to “BULK COLLECT into nested table”

Rajeshwaran, Jeyabal

October 24, 2016 at 11:47 pm

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 ?

Reply
connormcdonald

October 25, 2016 at 10:27 am

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.

Reply
Rupesh Kumar

October 27, 2016 at 3:17 am

Thanks Cornnor , It was very interesting to know

Reply
2017–what grabbed your attention | Learning is not a spectator sport

December 19, 2017 at 9:29 am

[…] BULK COLLECT into nested table […]

Reply
Susant Nayak

February 8, 2018 at 5:00 pm

Really this is the nice explain.. Thank you

Reply
PL/SQL Bulk Collect With LIMIT Clause In Oracle Database | RebellionRider

January 12, 2019 at 5:53 pm

[…] My dear friend Connor McDonald has done a blog explaining how you can overcome this problem using MULTISET. Go ahead and checkout his article. […]

Reply
Rob Savarese

October 11, 2020 at 5:43 am

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;
/

Reply