forall enhancements in 10g

FORALL was introduced in Oracle 8i as part of a set of new PL/SQL features for bulk fetching and binding. This short article demonstrates the enhancements made to FORALL in 10g. This article assumes that readers are familiar with the concepts of bulk PL/SQL and FORALL in particular (see the further reading section of this article for details of introductory papers).

Note that for simplicity, I've used the word "array" throughout this article as a collective term for collections and associative arrays (PL/SQL table, index-by tables). In almost all cases, they are interchangeable in bulk PL/SQL processing.

setup

We'll begin with a small table to serve as the target for the FORALL examples in this article.

SQL> CREATE TABLE tgt ( id INT, val VARCHAR2(128) );

Table created.

indices of

The INDICES OF clause allows us to load non-contiguous (sparse) arrays by telling Oracle to use just the elements that are populated. Remember in versions prior to 10g that arrays had to be dense and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to address them (these are still syntactically valid of course). The INDICES OF clause is simple to use as seen in the example below.

SQL> DECLARE
  2
  3     TYPE aat_rec IS TABLE OF tgt%ROWTYPE
  4        INDEX BY PLS_INTEGER;
  5     aa_recs aat_rec;
  6
  7  BEGIN
  8
  9     /* Load a sparse array... */
 10     SELECT object_id, object_name BULK COLLECT INTO aa_recs
 11     FROM   all_objects
 12     WHERE  ROWNUM <= 10;
 13
 14     aa_recs.DELETE(2);
 15     aa_recs.DELETE(4);
 16     aa_recs.DELETE(6);
 17
 18     /* Load table using sparse array... */
 19     FORALL i IN INDICES OF aa_recs
 20        INSERT INTO tgt
 21        VALUES aa_recs(i);
 22
 23     DBMS_OUTPUT.PUT_LINE(
 24        TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
 25        );
 26
 27  END;
 28  /

7 rows inserted.

PL/SQL procedure successfully completed.

Note that the array used in the INDICES OF clause does not necessarily have to be the one that is being loaded. Like all versions of FORALL, it is simply a driver to tell Oracle the indices to use in any arrays referenced in the subsequent DML statement. We can demonstrate this quite easily as follows.

SQL> DECLARE
  2
  3     TYPE aat_rec IS TABLE OF tgt%ROWTYPE
  4        INDEX BY PLS_INTEGER;
  5     aa_recs aat_rec;
  6
  7     TYPE aat_id IS TABLE OF PLS_INTEGER
  8        INDEX BY PLS_INTEGER;
  9     aa_ids aat_id;
 10
 11  BEGIN
 12
 13     /* Populate an array with data to be loaded... */
 14     SELECT object_id, object_name BULK COLLECT INTO aa_recs
 15     FROM   all_objects
 16     WHERE  ROWNUM <= 10;
 17
 18     /* Set the "driving array" indices... */
 19     aa_ids(1) := NULL; --value of element is irrelevant
 20     aa_ids(3) := NULL;
 21     aa_ids(5) := NULL;
 22
 23     /* Load table using sparse ID array as driver... */
 24     FORALL i IN INDICES OF aa_ids
 25        INSERT INTO tgt
 26        VALUES aa_recs(i);
 27
 28     DBMS_OUTPUT.PUT_LINE(
 29        TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
 30        );
 31
 32  END;
 33  /

3 rows inserted.

PL/SQL procedure successfully completed.

Note that in the previous example, we were only interested in the indices of the driving array. The values within each element were irrelevant; so irrelevant in fact, that we didn't even give them a value.

values of

The VALUES OF enables us to load just the elements of a data array where the indices match the values of a driving array (i.e. the data within the elements). In all probability this will be used far less than the INDICES OF clause, but it is worth covering here. The following example shows how we might load from the values within the elements of a driving array.

SQL> DECLARE
  2  
  3     TYPE aat_id IS TABLE OF PLS_INTEGER
  4        INDEX BY PLS_INTEGER;
  5     aa_ids aat_id;
  6  
  7     TYPE aat_rec IS TABLE OF tgt%ROWTYPE
  8        INDEX BY PLS_INTEGER;
  9     aa_recs aat_rec;
 10  
 11  BEGIN
 12  
 13     /*
 14      * Load up the "driving" array to say which
 15      * records from main array we want to use...
 16      */
 17     aa_ids(1) := 3;
 18     aa_ids(2) := 8;
 19     aa_ids(3) := 10;
 20  
 21     /* Load up some records into our array... */
 22     SELECT ROWNUM, object_name BULK COLLECT INTO aa_recs
 23     FROM   all_objects
 24     WHERE  ROWNUM <= 20;
 25  
 26     /*
 27      * Load table using indices determined from the VALUES of "driving"
 28      * array...
 29      */
 30     FORALL i IN VALUES OF aa_ids
 31        INSERT INTO tgt
 32        VALUES aa_recs(i);
 33  
 34     DBMS_OUTPUT.PUT_LINE(
 35        TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
 36        );
 37  
 38  END;
 39  /

3 rows inserted.

PL/SQL procedure successfully completed.

Now we can look at the data in the table to see that it is elements 3,8,10 that were loaded (i.e. the values in our driving array) and not the elements 1,2,3 (i.e. the indices of the driving array elements). We can see this because we fetched ROWNUM into the ID attribute of the data array.

SQL> SELECT * FROM tgt;

        ID VAL
---------- -----------------------------------
         3 SYSTEM_PRIVILEGE_MAP
         8 STMT_AUDIT_OPTION_MAP
        10 RE$NV_LIST

3 rows selected.

exceptions

The following is a small example of the exception we can expect if an index or value in the "driving" array doesn't exist in the "data" array. We'll contrive a driving array with an index beyond the bounds of the data array and then try to load our target table.

SQL> DECLARE
  2
  3     TYPE aat_rec IS TABLE OF tgt%ROWTYPE
  4        INDEX BY PLS_INTEGER;
  5     aa_recs aat_rec;
  6
  7     TYPE aat_id IS TABLE OF PLS_INTEGER
  8        INDEX BY PLS_INTEGER;
  9     aa_ids aat_id;
 10
 11  BEGIN
 12
 13     /* Load some data... */
 14     SELECT object_id, object_name BULK COLLECT INTO aa_recs
 15     FROM   all_objects
 16     WHERE  ROWNUM <= 10;
 17
 18     /* Set up a reference array with high key and value... */
 19     aa_ids(100) := NULL; --value irrelevant here
 20
 21     /* Now try to load... */
 22     FORALL i IN INDICES OF aa_ids
 23        INSERT INTO tgt
 24        VALUES aa_recs(i);
 25
 26  EXCEPTION
 27     WHEN OTHERS THEN
 28        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 29  END;
 30  /

ORA-22160: element at index [100] does not exist

PL/SQL procedure successfully completed.

Usefully, Oracle tells us the index it was trying to reference. The same exception and output would also be raised if we were to use the VALUES OF clause.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

oracle-developer.net