In Oracle PL/SQL, the FORALL statement is used to issue a series of static or dynamic DML statements. The FORALL is usually much faster than an equivalent FOR loop. In some cases processing may be up to 30 times as fast for operations such as INSERT operations.
When using FORALL each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses. This requires some setup in advance of using the FORALL statement.
The following restrictions apply when using FORALL:
- You cannot loop through the elements of an associative array that has a string type for the key.
- You cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement within a FORALL loop.
- The FORALL statement can be used only in server-side programs, not in client-side programs.
- The INSERT, UPDATE, or DELETE statement must reference at least one collection. A FORALL statement that inserts a set of constant values in a loop will raise an exception.
- When specifying an explicit range, all collection elements in that range must exist. Non-existent elements will produce an error.
- When you use the INDICES OF or VALUES OF clauses, all the collections referenced in the DML statement must have subscripts matching the values of the index variable.
- If you use the SAVE EXCEPTIONS clause, this error is treated like any other error and does not stop the FORALL statement.
- Individual record fields cannot be referred to within DML statements called by a FORALL statement. Instead, specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
- Collection subscripts must use only the index variable (such as i); they cannot use an expression such as i+1.
- The cursor attribute %BULK_ROWCOUNT cannot be assigned to other collections, nor can it be passed as a parameter to subprograms.
FORALL i IN 1..20
INSERT INTO test_table VALUES (user_id(i), first_name(i), last_name(i), ...);
Related Code Snippets:
- FORALL - FORALL transfers data from a PL/SQL collection to the specified table using collections. ...