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

Breadcrumb

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');

DECLARE
v_country nt_country_typ;
BEGIN
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;
END;
/

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>
SQL> create or replace type nt_emp
  2  as table of obj_emp;
  3  /

Type created.

SQL>
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>
SQL> declare
  2    type bulk_bind_array is table of nt_emp
  3      index by pls_integer;
  4
  5    single_entry nt_emp := nt_emp();
  6    bulk_entries bulk_bind_array;
  7
  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;
 16
 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;
 24
 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.

SQL>


and you rated our response

  (1 rating)

Reviews

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

PL/SQL

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