Bulk Binding : Error Handling

I covered here.
Bulk Binding : Bulk Collect & Forall

FORALL Exception Processing

If the EXCEPTION clause were not included in the PL/SQL routine, the first row processed by a FORALL statement with a problem would cause an un-handled exception issue for the routine and all DML work would be rolled back.

FORALL also handles exceptions, allowing for the successful rows/elements to be processed and the elements that caused problems to be tracked.

There are 2 methods of processing FORALL exceptions:

• Using the keywords ‘SAVE EXCEPTIONS’
• saves exceptions in another collection
• uses original table subscript to locate rows with errors

• Using the keywords ‘LOG ERRORS’
• uses a pre-defined table to store error rows
• table can be modified to hold additional information
• easier to code, perhaps
• does not increase the size of the PL/SQL routine

SAVE EXCEPTIONS
 
The FORALL exceptions are tracked in a collection called SQL%BULK_EXCEPTIONS. 


SQL%BULK_EXCEPTIONS.COUNT will contain the total number of exceptions encountered by the FORALL statement.

SQL%BULK_EXCEPTIONS(<subscript>).ERROR_INDEX will contain the Oracle error number.  The subscript will match up with the element causing the exception in the collection passed to the FORALL statement.

SQL%BULK_EXCEPTIONS(<subscript>).ERROR_CODE will contain the Oracle error message.

When displaying elements from the collection that caused the exceptions, use SQL%BULK_ROWCOUNT(<subscript>). 

This variable contains the rows affected by the DML for each element in the table.  If the rows affected are 0 then it is likely that this element caused an exception.

DECLARE
  v_error_count     NUMBER;
  TYPE EMPNO_TYPE IS TABLE OF EMP.EMPNO%TYPE;
  EMPNO_TABLE EMPNO_TYPE;
  
1: BEGIN
2:   SELECT EMPNO 
3:   BULK COLLECT into EMPNO_Table
4:   FROM EMP;
5:   DBMS_OUTPUT.PUT_LINE('Nested Table Rows Loaded = ' || EMPNO_Table.COUNT  );
6:    
    FORALL i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST 
7:   SAVE EXCEPTIONS
8:       UPDATE EMP set SAL = SAL / 0 WHERE EMPNO =  
9:           EMPNO_TABLE(i);
10: EXCEPTION
11:    WHEN OTHERS THEN
12:       v_error_count := SQL%BULK_EXCEPTIONS.COUNT;
13:       DBMS_OUTPUT.PUT_LINE('Errors Encountered = ' ||
14:        v_error_count);
15:       FOR i IN 1..v_error_count
16:       LOOP
17:          DBMS_OUTPUT.PUT_LINE('Empno: ' ||
              EMPNO_TABLE(i) || ' ' ||
              SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ':' ||
              SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
18:        END LOOP;
19:       DBMS_OUTPUT.PUT_LINE('EMPNO Not Updated: ');
20:       FOR i IN 1..EMPNO_TABLE.COUNT
21:       LOOP
22:          IF SQL%BULK_ROWCOUNT(i) = 0 THEN
23:             DBMS_OUTPUT.PUT(EMPNO_TABLE(i) || ' ');
24:          END IF;
25:       END LOOP;
26:       DBMS_OUTPUT.NEW_LINE;
27: END;

This illustration shows the EXCEPTION processing for a FORALL with obvious errors (notice the divide by zero on line 8).

The keywords ‘SAVE EXCEPTIONS’ at line 7 is part of the FORALL syntax.

Each of the above-mentioned variables appears in this EXCEPTION clause. 
Notice the first LOOP that will process the collection SQL%BULK_EXCEPTIONS. 
Notice that the SQLERRM is negated on line 17.  This is because Oracle error codes are negative numbers and this routine by default returns the Oracle error code in a positive number.
Notice the second LOOP (lines 21 thru 25) that prints out the affected elements that were originally passed to the FORALL statement.
  
 

 
 FORALL Save Exceptions Exception Processing Execution

This illustration shows the output from the prior code example.  EMP has 14 rows and since the divide by 0 was hard coded in the DML statement, each of the elements in the collection caused an exception in the DML.

LOG ERRORS
 
LOG ERRORS is the other option for processing exceptions from a FORALL statement.  This option puts the rows with exceptions into a ERR$_<table> for future processing. 

All rows without exceptions are committed to the database.  The PL/SQL routine does not return an error condition.

The ERR$_<table> is created ahead of time.  It contains the above-listed information.  This same table should be processed after doing the FORALL statement, possibly using a separate routine. 

The rows should then be deleted so as to not to be confused with future executions of the update routine.

BEGIN
   DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP',
                                 skip_unsupported => TRUE);
END;

Use this syntax to create the error log table for the EMP table.




Creating Error Log Table

Notice that all the EMP columns have a varchar2(4000) datatype.  Notice the name of the table now has the base table along with a ERR$_ prefix.

    FORALL i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
       UPDATE EMP set SAL = SAL / 0 WHERE EMPNO = EMPNO_TABLE(i)
       LOG ERRORS REJECT LIMIT UNLIMITED;

This syntax processes the EMP rows, again creating errors via a divide by zero.
 
 Contents of ERR$_EMP Table.

The exceptions were indeed recorded in the ERR$_EMP table.

Some additional information to this log table might be useful.  For example, if multiple programs are doing the FOR ALL … SAVE EXCEPTIONS, one might want to know who caused the error, when the error occurred, and which program logged the error.

It is OK to alter the ERR$ table and add columns. 

The SAVE EXECPTIONS does record an error condition to the PL/SQL routine and the exception clause is then coded to process the array created holding the Oracle error codes.
This option is probably better if not expecting a lot of excepted rows.

The LOG ERRORS does cause DML.  This log table will contain the Oracle error numbers and messages as well as the column data from the table being updated with the FORALL. 

This option does not make the PL/SQL routine larger (via another array) and this option does NOT return an error condition to the PL/SQL routine. 

The log table needs to be cleared prior to the FORALL statement and checked for exceptions after the FORALL statement.