Continuing Past Exceptions with FORALL

Oracle9i offers a new clause, SAVE EXCEPTIONS, which can be used inside a FORALL statement. By including this clause, you instruct Oracle to continue processing even when an error has occurred. Oracle will then "save the exception" (or multiple exceptions, if more than one error occurs). When the DML statement completes, it will then raise the ORA-24381 exception. In the exception section, you can then access a pseudo-collection called SQL%BULK_EXCEPTIONS to obtain error information.

Here is an example, followed by an explanation of what is going on:

/* File on web: bulkexc.sql */ 1 CREATE OR REPLACE PROCEDURE bulk_exceptions ( 2 whr_in IN VARCHAR2 := NULL) 3 IS 4 TYPE namelist_t IS TABLE OF VARCHAR2 (100); 5 enames_with_errors namelist_t := -- Max of 10 characters in emp. 6 namelist_t ('LITTLE', 'BIGBIGGERBIGGEST', 'SMITHIE', ''); 7 bulk_errors EXCEPTION; 8 PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 ); 9 BEGIN10 FORALL indx IN11 enames_with_errors.FIRST ..12 enames_with_errors.LAST13 SAVE EXCEPTIONS14 EXECUTE IMMEDIATE15 'UPDATE emp SET ename = :newname'16 USING enames_with_errors(indx);17 EXCEPTION18 WHEN bulk_errors19 THEN20 FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT21 LOOP22 DBMS_OUTPUT.PUT_LINE (23 'Error ' || indx || ' occurred during ' ||24 'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||25 ' updating name to ' ||26 enames_with_errors (27 SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX));28 DBMS_OUTPUT.PUT_LINE (29 'Oracle error is ' ||30 SQLERRM (-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));31 END LOOP;32 END;

When I run this code (with SERVEROUTPUT turned on), I see these results:

SQL> exec bulk_exceptions Error 1 occurred during iteration 2 updating name to BIGBIGGERBIGGESTOracle error is ORA-01401: inserted value too large for column Error 2 occurred during iteration 4 updating name toOracle error is ORA-01407: cannot update ( ) to NULL

In other words, Oracle encountered two exceptions as it processed the DML for the names collection. It did not stop with the first exception, but continued on, cataloguing a third.

The following table describes the error-handling functionality in this code:

Line(s)	Description
4-6	Declare and populate a collection that will drive the FORALL statement. I have intentionally placed data in the collection that will raise two errors.
8-9	Declare a named exception to make the exception section more readable.
11-17	Execute a dynamic UPDATE statement with FORALL using the enames_with_errors collection.
	Trap the "bulk exceptions error" by name. I could also have written code like: WHEN OTHERS THEN IF SQLCODE = -24381
	Use a numeric FOR loop to scan through the contents of the SQL%BULKEXCEPTIONS pseudo-collection. Note that I can call the COUNT method to determine the number of defined rows (errors raised), but I cannot call other methods, such as FIRST and LAST.
22-30	Extract the information from the collection and display (or log) error information.
	The ERROR_INDEX field of each pseudo-collection's row returns the row number in the driving collection of the FORALL statement for which an exception was raised.
	The ERROR_CODE field of each pseudo-collection's row returns the error number of the exception that was raised. Note that this value is stored as a positive integer; you will need to multiple it by -1 before passing it to SQLERRM or displaying the information.