BULLK COLLECT and BULK UPDATE/INSERT with collections

1. Bulk collect using collection of records
   declare
   type rec_type IS RECORD(c1 number, c2 varchar2(10), c3 varchar2(20));
   type nest_type4 IS TABLE OF rec_type;
   var4 nest_type4;
   begin
   SELECT col1, col2, col3
   BULK COLLECT INTO var4
   FROM test1;
   end;
   /


2. Bulk collect using collection of objects (Use object type name in select statement)
   --Need to create object and types as SQL type by declaring them in the ---database instead of declaring them with in PL/SQL code.
   create or replace type rec_obj as object
   (c1 number, c2 varchar2(10), c3 varchar2(20));
   
   create or replace type nest_type3 IS TABLE OF rec_obj;
   
   declare
   var4 nest_type3;
   begin
   SELECT rec_obj(col1, col2, col3)
   BULK COLLECT INTO var4
   FROM test1;
   end;
   /


3. Bulk insert/update/delete in FORALL statements using collection of records
   declare
   type rec_type IS RECORD(c1 number, c2 varchar2(10), c3 varchar2(20));
   type rec_type1 IS RECORD(c2 varchar2(10), c1 number, c3 varchar2(20));
   type nest_type4 IS TABLE OF rec_type;
   type nest_type5 IS TABLE OF rec_type1;
   var4 nest_type4;
   var5 nest_type5;
   begin
   SELECT col1, col2, col3
   BULK COLLECT INTO var4
   FROM test1;

FORALL i IN var4.first..var4.last
INSERT INTO (SELECT col2, col1, col3 from test2)
values var4(i);

SELECT col2, col1, col3
BULK COLLECT INTO var5
FROM test1;

FORALL i IN var5.first..var5.last
UPDATE test2
SET ROW=var5(i);
end;
/

4. Bulk insert/update/delete in FORALL statements using collection of objects
--Need to create object and types as SQL type by declaring them in the ---database instead of declaring them with in PL/SQL code.
create or replace type rec_obj as object
(c1 number, c2 varchar2(10), c3 varchar2(20));

create or replace type nest_type3 IS TABLE OF rec_obj;

declare
var4 nest_type3;
begin
SELECT rec_obj(col1, col2, col3)
BULK COLLECT INTO var4
FROM test1;

FORALL i IN var4.first..var4.last
INSERT INTO test2 (col2, col1, col3)
values (TREAT(var4(i) as rec_obj).c1,
TREAT(var4(i) as rec_obj).c2,
TREAT(var4(i) as rec_obj).c3);

FORALL i IN var4.first..var4.last
UPDATE test2
SET col2=TREAT(var4(i) as rec_obj).c1,
col1=TREAT(var4(i) as rec_obj).c2,
col3=TREAT(var4(i) as rec_obj).c3;
end;
/