web analytics
Handling Iteration Variable In FORALL Statements Options
codeling
Posted: Tuesday, June 27, 2017 1:40:04 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 777
Points: 3264

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

 

Sponsor
Posted: Tuesday, June 27, 2017 1:40:04 PM
 
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.