When doing bulk insertion it is very powerful to use FORALL. Advantages of FORALL is minimize switching PL/SQL engine and database. But it has some restrictions. You can not use iteration variable inside FORALL and can not reference fields of a collection. And you can not use iteration variable inside FORALL.
Let's give a demonstrating to this.
First creating a table
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR
SQL>
SQL> drop table coll;
Table dropped
SQL> create table coll(i number, z number);
Table created
Attempt to use iteration variable:
SQL> DECLARE
2 TYPE t_number IS TABLE OF NUMBER;
3 numbers t_number;
4 BEGIN
5 numbers := t_number();
6 numbers.EXTEND(10);
7 FOR i IN 1 .. 10 LOOP
8 numbers(i) := i;
9 END LOOP;
10 FORALL i IN numbers.FIRST .. numbers.LAST
11 INSERT INTO coll VALUES (i, numbers(i));
12 END;
13 /
DECLARE
TYPE t_number IS TABLE OF NUMBER;
numbers t_number;
BEGIN
numbers := t_number();
numbers.EXTEND(10);
FOR i IN 1 .. 10 LOOP
numbers(i) := i;
END LOOP;
FORALL i IN numbers.FIRST .. numbers.LAST
INSERT INTO coll VALUES (i, numbers(i));
END;
ORA-06550: line 11, column 30:
PLS-00430: FORALL iteration variable I is not allowed in this context
Second attempt to use iteration variable:
SQL> DECLARE
2 TYPE t_number IS TABLE OF NUMBER;
3 numbers t_number := t_number();
4 BEGIN
5 numbers.EXTEND(10);
6 FOR i IN 1 .. 10 LOOP
7 numbers(i) := i;
8 END LOOP;
9 FORALL i IN numbers.FIRST .. numbers.LAST
10 INSERT INTO coll VALUES ( nvl(i,-99), numbers(i) );
11 END;
12 /
DECLARE
TYPE t_number IS TABLE OF NUMBER;
numbers t_number := t_number();
BEGIN
numbers.EXTEND(10);
FOR i IN 1 .. 10 LOOP
numbers(i) := i;
END LOOP;
FORALL i IN numbers.FIRST .. numbers.LAST
INSERT INTO coll VALUES ( nvl(i,-99), numbers(i) );
END;
ORA-06550: line 10, column 36:
PLS-00430: FORALL iteration variable I is not allowed in this context
To clarify iteration value is NULL;
SQL> DECLARE
2 TYPE t_number IS TABLE OF NUMBER;
3 numbers t_number := t_number();
4 BEGIN
5 numbers.EXTEND(10);
6 FOR i IN 1 .. 10 LOOP
7 numbers(i) := i;
8 END LOOP;
9 FORALL i IN numbers.FIRST .. numbers.LAST
10 INSERT INTO coll VALUES (( SELECT nvl(i,-99) FROM dual), numbers(i));
11 END;
12 /
PL/SQL procedure successfully completed
SQL> SELECT * FROM coll;
I Z
---------- ----------
-99 1
-99 2
-99 3
-99 4
-99 5
-99 6
-99 7
-99 8
-99 9
-99 10
10 rows selected
To handle this situation it is possible to declare a colleciton:
SQL> ROLLBACK;
Rollback complete
SQL> DECLARE
2 TYPE t_number IS TABLE OF NUMBER;
3 numbers t_number := t_number();
4 numbers2 t_number := t_number();
5 BEGIN
6 numbers.EXTEND(10);
7 numbers2.EXTEND(10);
8 FOR i IN 1 .. 10 LOOP
9 numbers(i) := i;
10 numbers2(i) := i + 100;
11 END LOOP;
12 FORALL i IN numbers.FIRST .. numbers.LAST
13 INSERT INTO coll VALUES (numbers2(i), numbers(i));
14 END;
15 /
PL/SQL procedure successfully completed
SQL> SELECT * FROM coll;
I Z
---------- ----------
101 1
102 2
103 3
104 4
105 5
106 6
107 7
108 8
109 9
110 10
10 rows selected