The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop.

The SQL statement can reference more than one collection, but FORALL only improves performance where the index value is used as a subscript.

Usually, the bounds specify a range of consecutive index numbers. If the index numbers are not consecutive, such as after you delete collection elements, you can use the INDICES OF or VALUES OF clause to iterate over just those index values that really exist.

The INDICES OF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound.

The VALUES OF clause refers to a collection that is indexed by BINARY_INTEGER or PLS_INTEGER and whose elements are of type BINARY_INTEGER or PLS_INTEGER. The FORALL statement iterates over the index values specified by the elements of this collection.

Example 1 Issuing DELETE Statements in a Loop

This FORALL statement sends all three DELETE statements to the SQL engine at once:

CREATE TABLE employees2 AS SELECT * FROM employees;
DECLARE TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees2 WHERE department_id = depts(i);
COMMIT; END;
/
DROP TABLE employees2;

Example 2 Issuing INSERT Statements in a Loop

The following example loads some data into PL/SQL collections. Then it inserts the collection elements into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 500;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := dbms_utility.get_time;
FOR i IN 1..iterations LOOP -- use FOR loop INSERT INTO parts1 VALUES (pnums(i), pnames(i)); END LOOP;
t2 := dbms_utility.get_time;
FORALL i IN 1..iterations -- use FORALL statement INSERT INTO parts2 VALUES (pnums(i), pnames(i));
t3 := dbms_utility.get_time; dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));
dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));
COMMIT;
END;
/
DROP TABLE parts1; DROP TABLE parts2;

Executing this block should show that the loop using FORALL is much faster.

Example 3 Using FORALL with Part of a Collection

The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements:
CREATE TABLE employees2 AS SELECT * FROM employees;
DECLARE TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN FORALL j IN 4..7 -- use only part of varray DELETE FROM employees2 WHERE department_id = depts(j);
COMMIT;
END;
/
DROP TABLE employees2;

Example 4 Using FORALL with Non-Consecutive Index Values

You might need to delete some elements from a collection before using the collection in a FORALL statement. The INDICES OF clause processes sparse collections by iterating through only the remaining elements.

You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the VALUES OF clause lets you set up simple collections whose elements serve as "pointers" to elements in the original collection.

The following example creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that would not work in a default FORALL statement. The program uses a FORALL statement with the INDICES OF clause to insert the data into a table. It then sets up two more collections, pointing to certain elements from the original collection. The program stores each set of names in a different database table using FORALL statements with the VALUES OF clause.

-- Create empty tables to hold order details
CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;

DECLARE
-- Make collections to hold a set of customer names and order amounts.

   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLe OF cust_name;
   cust_tab cust_typ;

   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;

-- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();

   PROCEDURE setup_data IS BEGIN
      -- Set up sample order data, including some invalid orders and some 'big' orders.
      cust_tab := cust_typ('Company 1','Company 2','Company 3','Company 4', 'Company 5');
      amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
   END;

BEGIN
   setup_data();

   dbms_output.put_line('--- Original order data ---');
   FOR i IN 1..cust_tab.LAST LOOP
      dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i));
   END LOOP;

-- Delete invalid orders (where amount is null or 0).
   FOR i IN 1..cust_tab.LAST LOOP
      IF amount_tab(i) is null or amount_tab(i) = 0 THEN
         cust_tab.delete(i);
         amount_tab.delete(i);
      END IF;
   END LOOP;

   dbms_output.put_line('--- Data with invalid orders deleted ---');
   FOR i IN 1..cust_tab.LAST LOOP
      IF cust_tab.EXISTS(i) THEN
         dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i));
      END IF;
   END LOOP;

-- Since the subscripts of our collections are not consecutive, we use
-- FORALL...INDICES OF to iterate through the actual subscripts, rather than 1..COUNT.
   FORALL i IN INDICES OF cust_tab
      INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i));

-- Now let's process the order data differently. We'll extract 2 subsets
-- and store each subset in a different table.

   setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.

   FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
      IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
         rejected_order_tab.EXTEND; -- Add a new element to this collection.
         rejected_order_tab(rejected_order_tab.LAST) := i; -- And record the subscript from the original collection.
      END IF;
      IF amount_tab(i) > 2000 THEN
         big_order_tab.EXTEND; -- Add a new element to this collection.
         big_order_tab(big_order_tab.LAST) := i; -- And record the subscript from the original collection.
      END IF;
   END LOOP;

-- Now it's easy to run one DML statement on one subset of elements, and another DML statement on a different subset.

   FORALL i IN VALUES OF rejected_order_tab
      INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));

   FORALL i IN VALUES OF big_order_tab
      INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));

   COMMIT;
END;
/
-- Verify that the correct order details were stored.
SELECT cust_name "Customer", amount "Valid order amount"  FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount"  FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount"  FROM rejected_orders;

DROP TABLE valid_orders;
DROP TABLE big_orders;
DROP TABLE rejected_orders;

How FORALL Affects Rollbacks

In a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as follows. Then, you change the job titles so that they are longer. The second UPDATE fails because the new value is too long for the column. Because we handle the exception, the first UPDATE is not rolled back and we can commit that change.

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(18));
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30);
BEGIN
   INSERT INTO emp2 VALUES(10, 'Clerk');
   INSERT INTO emp2 VALUES(20, 'Bookkeeper');  -- Lengthening this job title causes an exception.
   INSERT INTO emp2 VALUES(30, 'Analyst');
   COMMIT;

   FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements.
      UPDATE emp2 SET job = job || ' (Senior)' WHERE deptno = depts(j);
         -- raises a "value too large" exception
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line('Problem in the FORALL statement.');
      COMMIT; -- Commit results of successful updates.
END;
/
DROP TABLE emp2;

Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute

The cursor attributes SQL%FOUND, SQL%ISOPEN, SQL%NOTFOUND, and SQL%ROWCOUNT, return useful information about the most recently executed DML statement.

The SQL cursor has one composite attribute, %BULK_ROWCOUNT, for use with the FORALL statement. This attribute works like an associative array: SQL%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an INSERT, UPDATE or DELETE statement. For example:

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(30, 50, 60);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp2 WHERE department_id = depts(j);
-- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      dbms_output.put_line('Iteration #' || i || ' deleted ' ||
         SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/
DROP TABLE emp2;

The FORALL statement and %BULK_ROWCOUNT attribute use the same subscripts. For example, if FORALL uses the range 5..10, so does %BULK_ROWCOUNT. If the FORALL tatement uses the INDICES OF clause to process a sparse collection, %BULK_ROWCOUNT has corresponding sparse subscripts. If the FORALL statement uses the VALUES OF clause to process a subset of elements, %BULK_ROWCOUNT has subscripts corresponding to the values of the elements in the index collection. If the index collection contains duplicate elements, so that some DML statements are issued multiple times using the same subscript, then the corresponding elements of %BULK_ROWCOUNT represent the sum of all rows affected by the DML statement using that subscript. (For examples showing how to interpret %BULK_ROWCOUNT when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://otn.oracle.com/tech/pl_sql/.)

%BULK_ROWCOUNT is usually equal to 1 for inserts, because a typical insert operation affects only a single row. For the INSERT ... SELECT construct, %BULK_ROWCOUNT might be greater than 1. For example, the FORALL statement below inserts an arbitrary number of rows for each iteration. After each iteration, %BULK_ROWCOUNT returns the number of items inserted:

CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
   FROM employees WHERE 1 = 0;
DECLARE
  TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
  deptnums dept_tab;
BEGIN
  SELECT department_id BULK COLLECT INTO deptnums FROM departments;

  FORALL i IN 1..deptnums.COUNT
     INSERT INTO emp_by_dept
        SELECT employee_id, department_id FROM employees
           WHERE department_id = deptnums(i);

  FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
     dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
                          SQL%BULK_ROWCOUNT(i)||' records');
  END LOOP;

  dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
END;
/
DROP TABLE emp_by_dept;

You can also use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT after running a FORALL statement. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement.

%FOUND and %NOTFOUND refer only to the last execution of the SQL statement. You can use %BULK_ROWCOUNT to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.

Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute

PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.

To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement.

All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:


%BULK_EXCEPTIONS(i).ERROR_INDEX holds the "iteration" of the FORALL statement during which the exception was raised.


%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.

The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.

You might need to work backward to determine which collection element was used in the iteration that caused an exception. For example, if you use the INDICES OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to %BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the VALUES OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches %BULK_EXCEPTIONS(i).ERROR_INDEX, and then use that element's value as the subscript to find the erroneous element in the original collection. (For examples showing how to find the erroneous elements when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://otn.oracle.com/tech/pl_sql/.)

If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.


Example 5 Bulk Operation That Continues Despite Exceptions

The following example shows how you can perform a number of DML operations, without stopping if some operations encounter errors:

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
   errors  NUMBER;
   dml_errors EXCEPTION;
   PRAGMA exception_init(dml_errors, -24381);
BEGIN
-- SAVE EXCEPTIONS means don't stop if some DELETEs fail.
   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
      DELETE FROM emp2 WHERE salary > 500000/num_tab(i);
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
  WHEN dml_errors THEN -- Now we figure out what failed and why.
   errors := SQL%BULK_EXCEPTIONS.COUNT;
   dbms_output.put_line('Number of DELETE statements that failed: ' || errors);
   FOR i IN 1..errors LOOP
      dbms_output.put_line('Error #' || i || ' occurred during '||
         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      dbms_output.put_line('Error message is ' ||
         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/
DROP TABLE emp2;

In this example, PL/SQL raised the predefined exception ZERO_DIVIDE when i equaled 2, 6, 10. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returned 3, and the contents of SQL%BULK_EXCEPTIONS were (2,1476), (6,1476), and (10,1476). To get the Oracle error message (which includes the code), we negated the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE and passed the result to the error-reporting function SQLERRM, which expects a negative number. Here is the output:

Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero