Process non-consecutive array items in FORALL

Executing Data Manipulation Language (DML) statements within a PL/SQL loop is time consuming because each time through the loop a switch is made from the PL/SQL engine to the SQL engine. A better approach is to use the FORALL statement, which passes the SQL statement only once along with a collection of fill-in values.

Prior to Oracle 10g, the syntax of the FORALL statement only permitted a consecutive range of array elements to be processed:

FORALL index_name IN lower_bound ..upper_bound
 sql_statement;

This meant that nested tables used with FORALL could not have deleted elements in the middle of the range to be processed, and that array items had to be processed in sequential order. Both of these restrictions have been lifted in Oracle 10g, with the addition of the INDICES OF and VALUES OF clauses.

The INDICES OF clause replaces the lower_bound ..upper_bound to indicate that all valid index values should be processed, even if there are gaps. It looks like this:

FORALL index_name IN INDICES OF collection_name
 BETWEEN lower_bound AND upper_bound
 sql_statement;

You can still limit the range processed by using the BETWEEN syntax, which is optional.

The VALUES OF clause enables you to process the main collection in a different sequence. You create a second collection containing only the index numbers you want to process, in the order you want them processed. The statement then becomes:

FORALL index_name IN VALUES OF index_collection
 sql_statement;

Listing A shows an example using the HR sample schema. I load department names into a nested table in memory, and then search it for IT-related departments. For each one, I save the index of its table entry. The VALUES OF clause is used with this set of indices to process an INSERT statement for each department in the list. (This is just a demonstration; the same thing could be done in a single SQL statement.) Listing B shows the output from the run.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.