oracle-developer.net

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.

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.

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.

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.

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.