[Chapter 5] 5.2 Bulk DML with the FORALL Statement

5.2 Bulk DML with the FORALL Statement

PL/SQL has a new keyword: FORALL. This keyword tells the PL/SQL runtime engine to bulk bind into the SQL statement all the elements of one or more collections before sending anything to the SQL engine.

Although the FORALL statement contains an iteration scheme (it iterates through all the rows of a collection), it is not a FOR loop. It does not, consequently, have either a LOOP or an END LOOP statement. Its syntax is as follows:

FORALL 
index_row
 IN 
lower_bound
 ... 
upper_bound

   
sql_statement
;

index_row: The specified collection; the FORALL will iterate through the rows of this collection
lower_bound: The starting index number (row or collection element) for the operation
upper_bound: The ending index number (row or collection element) for the operation
sql_statement: The SQL statement to be performed on each collection element

You must follow these rules when using FORALL:

The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE.
The DML must reference collection elements, indexed by the index_row variable in the FORALL statement. The scope of the index_row variable is the FORALL statement only; you may not reference it outside of that statement.
Do not declare an INTEGER variable for index_row . It is declared implicitly by the PL/SQL engine.
The lower and upper bounds must specify a valid range of consecutive index numbers for the collection(s) referenced in the SQL statement. The following script, for example:
```
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   ceo_payoffs NumList := 
      NumList(1000000, 42000000, 20000000, 17900000);
BEGIN
   ceo_payoffs.DELETE(3);  -- delete third element
   FORALL indx IN ceo_payoffs.FIRST..ceo_payoffs.LAST
      UPDATE excessive_comp
         SET salary = ceo_payoffs(indx)
       WHERE layoffs > 10000;
END;
```
will cause the following error:
```
ORA-22160: element at index [3] does not exist
```
This error occurs because the DELETE method has removed an element from the collection; the FORALL statement requires a densely filled collection. See the diffcount.sql file on the companion disk for an example (and resulting behavior) of this scenario.

The collection subscript referenced in the DML statement cannot be an expression. For example, the following script:

DECLARE
   names name_varray := name_varray();
BEGIN
   FORALL indx IN names.FIRST .. names.LAST
      DELETE FROM emp WHERE ename = names(indx+10);
END;
/

will cause the following error:

PLS-00430: FORALL iteration variable INDX is not allowed in this context

The DML statement can reference more than one collection. The upper and lower bounds do not have to span the entire contents of the collection(s). When this statement is bulk bound and passed to SQL, the SQL engine executes the statement once for each index number in the range. In other words, the same SQL statements will be executed, but they will all be run in the same round-trip to the SQL layer, minimizing the context switches, as shown in Figure 5.3 .

Figure 5.3: One context switch with FORALL

5.2.1 FORALL Examples

Here are some examples of the use of the FORALL statement:

Let's rewrite the update_tragedies procedure to use FORALL:
```
CREATE OR REPLACE PROCEDURE update_tragedies (
   warcrim_ids IN name_varray,
   num_victims IN number_varray
   )
IS
BEGIN
   FORALL indx IN warcrim_ids.FIRST .. warcrim_ids.LAST 
      UPDATE war_criminal
         SET victim_count = num_victims (indx)
       WHERE war_criminal_id = warcrim_ids (indx);
END;
```
Notice that the only changes in this example are to change FOR to FORALL, and to remove the LOOP and END LOOP keywords. This use of FORALL accesses and passes to SQL each of the rows defined in the war criminals array (and the corresponding values for the number of victims).
In the following call to FORALL, I am passing a subset of the collection's full range of values to the SQL statement:

DECLARE
   TYPE not_enough_names IS VARRAY(2000) OF VARCHAR2(100);
   usda_inspectors not_enough_names := not_enough_names ();
BEGIN
   -- Fill varray with the names of the US Department of Agriculture
   -- inspectors who are supposed to inspect over 7,000,000 businesses
   -- in the United States. 
   ...

   -- Make government smaller: DELETE all but 100 inspectors
   -- and then wait for the E. coli to attack.
   FORALL indx IN 101 .. usda_inspectors.LAST 
      DELETE FROM federal_employee
       WHERE name = usda_inspectors(indx);
END;

The previous example shows how the DML statement can reference more than one collection. In this next case, I have three: denial, patient_name, and illnesses. Only the first two are subscripted. Since the PL/SQL engine bulk binds only subscripted collections, the same illnesses collection is inserted as a whole into the hmo_coverage table for each of the rows inserted:

FORALL indx IN denial.FIRST .. denial.LAST
   INSERT INTO hmo_coverage 
      VALUES (denial(indx), patient_name(indx), illnesses);

5.2.2 ROLLBACK Behavior with FORALL

The FORALL statement allows you to pass multiple SQL statements all together (in bulk) to the SQL engine. This means that as far as context switching is concerned, you have one SQL "block," but these blocks are still treated as individual DML operations.

What happens when one of those DML statements fails? The following rules apply:

The FORALL statement stops executing. It isn't possible to request that the FORALL skip over the offending statement and continue on to the next row in the collection.
The DML statement being executed is rolled back to an implicit savepoint marked by the PL/SQL engine before execution of the statement.
Any previous DML operations in that FORALL statement that already executed without error are not rolled back.

The following script demonstrates this behavior; it's available in the forallerr.sql file on the companion disk.

First, I create a table for lobbyists of the National Rifle Association (if they are not "gun happy," then I don't know who is) and fill it with some gun information:

/* Filename on companion disk: forallerr.sql */
CREATE TABLE gun_happy (
   name VARCHAR2(15), country VARCHAR2(100), killed INTEGER);
INSERT INTO gun_happy VALUES('AK-47', 'Russia', 100000);
INSERT INTO gun_happy VALUES('Uzi', 'Israel', 50000);  
INSERT INTO gun_happy VALUES('Colt-45', 'USA', 25000000);

Then I use FORALL to update the names of the guns to include the number of people killed by those guns. (Whoops! Guns don't kill people. People kill people.)

DECLARE
   TYPE StgList IS TABLE OF VARCHAR2(100);
   countries StgList := StgList ('Israel', 'Russia', 'USA');
BEGIN
   FORALL indx IN countries.FIRST..countries.LAST
      UPDATE gun_happy SET name = name || '-' || killed 
         WHERE country = countries(indx);
         
   DBMS_OUTPUT.PUT_LINE ('Update performed!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Update did not complete!');
      COMMIT;
END; 
/

Take note of two things:

I place the countries in the nested table named "countries" in alphabetical order; thus, the update for the Colt .45 will be the last one processed.
When I concatenate the name and killed tables for the Colt .45, the length of this string exceeds 15 characters. This will raise a VALUE_ERROR exception.

To see the impact of this block, I run my script with queries to show the contents of the gun_happy table:

SQL> @forallerr

Gun Names
---------------
AK-47
Uzi
Colt-45

Use FORALL for update...
Update did not complete!

Gun Names
---------------
AK-47-100000
Uzi-50000
Colt-45

As you can see, the first two changes stuck, whereas the last attempt to change the name failed, causing a rollback, but only to the beginning of that third UPDATE statement.

How do you know how many of your DML statements succeeded? You can check the SQL%BULK_ROWCOUNT cursor attribute; this new attribute, used exclusively with bulk binds, is discussed later in Section 5.4, "Using Cursor Attributes .