Nov
23
Written by:
StevenFeuersteinTW
Monday, November 23, 2009 8:44 AM
FORALL was introduced into PL/SQL in Version 8i. It is a fantastic feature; you should use it in place of all loops that contain DML statements performing row-by-row processing. You will generally see performance improvements of at least an order of magnitude.
I assume a working knowledge of FORALL for the remainder of this post.
The typical (and, in Oracle8i and Oracle9i, the only) way to construct a FORALL statement is to use a header that is very similar to a numeric FOR loop. Here is an example:
DECLARE
TYPE namelist_t IS TABLE OF employees.first_name%TYPE;
l_enames namelist_t := namelist_t ('ABC', 'DEF', 'SMITHIE');
BEGIN
FORALL indx IN 1 .. l_enames.COUNT
UPDATE employees
SET first_name = l_enames (indx);
END;
More generally, the FORALL header looks like this:
FORALL integer_index IN low_value .. high_value
where integer_index is an implicitly declared integer iterator, low_value is the low end of the integer range and high_value is the high end. Each integer between low and high must reference a defined index value in any of the collections that are bound into the FORALL's DML statement (in the above case, there is just one: l_enames).
In other words, the binding array must be densely filled between the low and high values.
If the collection is sparse and the FORALL statement tries to read an element at an undefined element, it will raise an exception as shown below:
Notice that it does not raise NO_DATA_FOUND. I point this out because it seems like Oracle goes out of its way to raise this exception whenever the error bears even the remotest resemblance to "I tried to get something and it wasn't there" (SELECT INTO, read an element in a collection at an undefined index value, read past the end of a file).
Notice also that even if I include the SAVE EXCEPTIONS clause in my FORALL statement, this exception is not "saved" to the SQL%BULK_EXCEPTIONS pseudo-collection. That's because this is a "meta-error" for the FORALL statement. The exception is not raised by the SQL engine as it performs the DML statement. It is, instead, raised by the PL/SQL engine before the SQL statements are passed along to the SQL engine.
So what's a programmer to do if your binding array is not densely-filled?
In Oracle10g Release 2, Oracle added support for two new ways to construct the FORALL header: INDICES OF and VALUES OF. With either of these, your FORALL header no longer looks like a numeric FOR loop. Instead you specify that you want the FORALL statement to reference only those elements in the binding array whose index value is defined in the INDICES OF array.
The simplest form uses the same collection as the binding and INDICES OF arrays, as in:
FORALL indx IN INDICES OF l_enames
UPDATE employees SET first_name = l_enames (indx);
Now, an update statement will be sent to the PL/SQL engine only for this index values that are defined in the collection.
But the collection referenced in the INDICES OF clause need not be the same as the binding array. Here is an example of using INDICES OF with a distinct collection:
DECLARE
TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
INDEX BY PLS_INTEGER;
l_employees employee_aat;
TYPE boolean_aat IS TABLE OF BOOLEAN
INDEX BY PLS_INTEGER;
l_employee_indices boolean_aat;
BEGIN
l_employees (1) := 137;
l_employees (100) := 126;
l_employees (500) := 147;
l_employee_indices (1) := FALSE;
l_employee_indices (500) := TRUE;
l_employee_indices (799) := NULL;
FORALL l_index IN INDICES OF l_employee_indices
BETWEEN 1 AND 500
UPDATE employees
SET salary = 10000
WHERE employee_id = l_employees (l_index);
END;
Updates will be run only for employee IDs 137 and 147.
The VALUES_OF clause offers an additional layer of "indirection." With VALUES_OF, the FORALL statement executes a DML statement for each index value in the binding array that is an element in (not an index value in) the VALUES OF collection.
Here is an example:
DECLARE
TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
INDEX BY PLS_INTEGER;
l_employees employee_aat;
TYPE indices_aat IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
l_employee_indices indices_aat;
BEGIN
l_employees (-77) := 134;
l_employees (13067) := 123;
l_employees (99999999) := 147;
l_employees (1070) := 429;
l_employee_indices (100) := -77;
l_employee_indices (200) := 13067;
l_employee_indices (300) := 1070;
FORALL l_index IN VALUES OF l_employee_indices
UPDATE employees
SET salary = 10000
WHERE employee_id = l_employees (l_index);
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
In this block of code, an UPDATE is executed for employee IDs134, 123 and 429.
So if you are using FORALL but running into situations where your binding array may not be sequentially filled, give INDICES OF or VALUES_OF a try. It might simplify your life and code dramatically.