FORALL Statement

The FORALL statement instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".

Syntax

Keyword and Parameter Description

index_name

This is an undeclared identifier that can be referenced only within the FORALL statement and only as a collection subscript.

The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.

lower_bound .. upper_bound

These are expressions that must yield number, which, if necessary, PL/SQL rounds to the nearest integer. The integers must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated only when the FORALL statement is first entered.

SAVE EXCEPTIONS

These optional keywords cause the FORALL loop to continue even if some DML operations fail. The details of the errors are available after the loop in SQL%BULK_EXCEPTIONS. The program can report or clean up all the errors after the FORALL loop, rather than handling each exception as it happens.

sql_statement

This must be an INSERT, UPDATE, or DELETE statement that references collection elements.

Usage Notes

The SQL statement can reference more than one collection. However, the performance benefits apply only to subscripted collections.

If a FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous iterations of the FORALL loop are not rolled back.

Restrictions

The following restrictions apply to the FORALL statement:

You cannot loop through the elements of an associative array that has a string type for the key.

Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.

You can use the FORALL statement only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.

The INSERT, UPDATE, or DELETE statement must reference at least one collection. For example, a FORALL statement that inserts a set of constant values in a loop raises an exception.

All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error. An example follows: