Skip to Main Content
  • Questions
  • bulk operation on multilevel collection -


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Baby.

Asked: May 27, 2020 - 4:47 pm UTC

Answered by: Connor McDonald - Last updated: May 28, 2020 - 2:58 am UTC

Category: PL/SQL - Version: 12c

Viewed 100+ times

You Asked

Example 3-22 Using UPDATE to Insert an Entire Multilevel Collection

INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');

v_country nt_country_typ;
v_country := nt_country_typ( country_typ(
'US', 'United States of America', nt_location_typ (
location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
UPDATE region_tab r
SET r.countries = v_country WHERE r.region_id = 2;

The above is from the oracle doc.

Qn - is it possible to do bulk insert operation in multilevel collection ...similar to

forall i in 1...
forall j in 1..
insert into .....values tabtype(i).xxx, tabtype(j).yyy ...

and we said...

Not really, because the thing you are bulk updating is a *row*.

So FORALL will work fine, but it is for multiple instances of the (in your case) NT_COUNTRY_TYP objects. For example.

SQL> create or replace type obj_emp as object (
  2    empno int,
  3    ename varchar2(20)
  4  );
  5  /

Type created.

SQL> create or replace type nt_emp
  2  as table of obj_emp;
  3  /

Type created.

SQL> create table emp_obj_tab
  2  ( dept int,
  3    emp_list nt_emp
  4  )
  5  nested table emp_list STORE AS emp_obj_tab_list
  6  ;

Table created.

SQL> declare
  2    type bulk_bind_array is table of nt_emp
  3      index by pls_integer;
  5    single_entry nt_emp := nt_emp();
  6    bulk_entries bulk_bind_array;
  8    deptlist sys.odcinumberlist := sys.odcinumberlist(10,20,30,40);
  9  begin
 10    --
 11    -- one instance with multiple entries
 12    --
 13    select obj_emp(empno,ename)
 14    bulk collect into single_entry
 15    from scott.emp;
 17    --
 18    -- simulate multiple instances
 19    --
 20    bulk_entries(1) := single_entry;
 21    bulk_entries(2) := single_entry;
 22    bulk_entries(3) := single_entry;
 23    bulk_entries(4) := single_entry;
 25    forall i in 1 ..   bulk_entries.count
 26      insert into emp_obj_tab values (deptlist(i), bulk_entries(i));
 27  end;
 28  /

PL/SQL procedure successfully completed.


and you rated our response

  (1 rating)


Multi level collection

May 28, 2020 - 3:48 am UTC

Reviewer: Apr from NYC, USA

Thanks for the example - very useful. We have performance issue doing single record by record insert in a loop from a multi level collection variable into 2 tables. There are 2 loops involved because of multi level - both loop consisting of a single record insert loop . I was wondering whether we could use forall bulk insert for both the loops for performance gains

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.