FOR LOOP EXECUTE IMMEDIATE VS FORALL EXECUTE IMMEDIATE

Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE.

This is just for example purpose only - FORALL is really useful for bulk processing. Though in the below example a direct FORALL - INSERT is possible, have used EXECUTE IMMEDATE to demonstrate its use.

TABLE CREATION SCRIPTS

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum < 201

drop table coll_temp_target

CREATE TABLE coll_temp_target AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum = 5

STEP 1 - USING FORALL

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FORALL i IN 1 .. status_tab.count
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);

END;
/

STEP 2 - USING FOR.. LOOP

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FOR i IN 1 .. status_tab.count LOOP
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);
END LOOP;

END;
/

SQL> @exec.sql

PL/SQL procedure successfully completed.

SQL> select * from coll_temp_target;

STATUS OBJECT_ID OWNER
------- ---------- ------------------------------
VALID 79400 SYS
VALID 69022 SYS
VALID 84837 SYS
VALID 75686 SYS
VALID 84402 SYS
VALID 71636 SYS
VALID 85043 SYS
VALID 85098 SYS
VALID 71312 SYS
VALID 73222 SYS

10 rows selected.