Thanks for the question, Baby.
Asked: May 27, 2020 - 4:47 pm UTC
Last updated: May 28, 2020 - 2:58 am UTC
Version: 12c
Viewed 1000+ 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 Connor 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>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment