Handling Iteration Variable In FORALL Statements

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


Lastly, i want to show other restiriction of FORALL

SQL> DECLARE
  2    TYPE t_number_rec IS RECORD(
  3      i NUMBER,
  4      z NUMBER);
  5    TYPE t_number_tab IS TABLE OF t_number_rec;
  6    numbers t_number_tab := t_number_tab();
  7  BEGIN
  8    numbers.EXTEND(10);
  9    FOR i IN 1 .. 10 LOOP
 10      numbers(i).i := i;
 11      numbers(i).z := i + 100;
 12    END LOOP;
 13    FORALL i IN numbers.FIRST .. numbers.LAST
 14      INSERT INTO coll VALUES (numbers(i).i, numbers(i).z);
 15  END;
 16  /

DECLARE
  TYPE t_number_rec IS RECORD(
    i NUMBER,
    z NUMBER);
  TYPE t_number_tab IS TABLE OF t_number_rec;
  numbers t_number_tab := t_number_tab();
BEGIN
  numbers.EXTEND(10);
  FOR i IN 1 .. 10 LOOP
    numbers(i).i := i;
    numbers(i).z := i + 100;
  END LOOP;
  FORALL i IN numbers.FIRST .. numbers.LAST
    INSERT INTO coll VALUES (numbers(i).i, numbers(i).z);
END;

ORA-06550: line 14, column 30:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 14, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 44:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 14, column 44:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 30:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored