Handling Iteration Variable In FORALL Statements Options

codeling Posts: 1057 Points: 4443
Posted: Tuesday, June 27, 2017 1:40:04 PM

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

 

 
Users browsing this topic
Guest