On Avoiding Termination

Continue PL/SQL execution beyond exceptions.

March/April 2009

I have been assigned the job of modifying an existing procedure that applies a complex set of rules to a large volume of data in a set of tables. In the past, as soon as an error occurred in an update, the procedure would terminate execution. Now I need to change the procedure so that it continues past any exceptions and completes as many updates as possible. What are my options in PL/SQL for doing this?

In Oracle Database 10g and higher, PL/SQL offers three options for “continuing past an exception,”which really means avoiding termination of the execution of the current block.

1. The nested block. You enclose the lines of code that may raise the exception inside a BEGIN END nested block. Then add an exception section so that the error is trapped and handled. Then keep on going.
2. The FORALL SAVE EXCEPTIONS clause. Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL. FORALL SAVE EXCEPTIONS suppresses exceptions at the generated statement level, so if that statement’s change of a row raises an error, changes to other rows already completed by that generated statement are also rolled back. When the FORALL statement is completed, Oracle Database will then raise the ORA-21438 error if at least one exception was encountered.
3. Data manipulation language (DML) error logging. Use the DBMS_ERRLOG package and LOG ERRORS with any insert, update, delete, or merge statements (within or independent of FORALL) to suppress exceptions at the row level. If a statement’s change of one row raises an error, changes to other rows already made by that statement will not be rolled back and Oracle Database will continue to change any remaining rows identified by that statement. DBMS_ERRLOG and LOG ERRORS will then write error information out to an error log table.

In this column, I take a look at each of these approaches and describe their strengths and weaknesses.

In each case, I will implement the following logic: For each employee in a given department, increase that person’s salary by a specified percentage after applying any necessary adjustments. A database trigger places additional constraints on the salary, so any individual update for an employee might fail. If it does, log the error and continue processing the remaining employees.

The salary adjustment procedure (the implementation of which is not shown in this column because it is irrelevant) has the following header:

PACKAGE compensation_rules
IS
   FUNCTION adjusted_compensation (
      employee_id_in IN
employees.employee_id%TYPE,
      pct_increase_in IN NUMBER)
   RETURN
 employees.salary%TYPE
   ;
END compensation_rules;

My nested block implementation of this functionality is shown in the CHANGE_SALARY_FOR procedure in Listing 1. With this approach, I fetch one row at a time from the employees table, using a cursor FOR loop. I place all the code within the loop’s body inside a nested block, which includes an exception section.

Code Listing 1: CHANGE_SALARY_FOR using nested block

PROCEDURE change_salary_for (
   dept_in            IN employees.department_id%TYPE
 , pct_increase_in IN NUMBER
)
IS
   CURSOR employees_cur
   IS
      SELECT employee_id, salary
        FROM employees
      WHERE department_id = dept_in;
BEGIN
   FOR rec IN employees_cur
   LOOP
      BEGIN
         rec.salary := compensation_rules.adjusted_compensation (
            employee_id_in    => rec.employee_id
          , pct_increase_in   => pct_increase_in
         );
         UPDATE employees
               SET salary = rec.salary
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            q$error_manager.raise_unanticipated (
               name1_in    => 'EMPLOYEEE_ID'
             , value1_in    => rec.employee_id
             , name2_in    => 'PCT_INCREASE'
             , value2_in    => pct_increase_in
             , name3_in    => 'NEW_SALARY'
             , value3_in    => rec.salary
            );
      END;
   END LOOP;
END change_salary_for;

In the executable section of this nested block, I adjust the compensation and then perform the update for that single row. If the update raises an exception, I immediately trap the exception (still within the loop body) and log the error.

Because I do not reraise the exception once the error is logged, the cursor FOR loop then retrieves the next employee row and I continue applying the raise percentage. This procedure demonstrates how I can continue past an exception by using a nested block to stop the propagation of an exception, handle it, and then continue executing statements within the enclosing block’s executable section.

That “traditional”nested block approach is very readable and quite elegant in its formulation, but it retrieves and then updates data row by row. This approach is fine if the volume of data you are working with is small, but for larger data sets this approach can be unacceptably slow. For this reason, Oracle introduced the BULK COLLECT and FORALL statements in Oracle8i Database. BULK COLLECT enables you to retrieve multiple rows of data with a single context switch to the SQL engine, and FORALL changes multiple rows (insert, update, delete, or merge) with a single context switch.

In the revised CHANGE_SALARY_FOR procedure in Listing 2, I perform the required salary adjustment operations in the following steps:

Code Listing 2: CHANGE_SALARY_FOR using FORALL and SAVE EXCEPTIONS

CREATE OR REPLACE PROCEDURE change_salary_for (
   dept_in            IN employees.department_id%TYPE
 , pct_increase_in IN NUMBER
 , fetch_limit_in  IN PLS_INTEGER
)
IS
   bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   CURSOR employees_cur
   IS
      SELECT employee_id, salary
        FROM employees
       WHERE department_id = dept_in;
   TYPE employee_tt
   IS
      TABLE OF employees.employee_id%TYPE
         INDEX BY BINARY_INTEGER;
   employee_ids   employee_tt;
   TYPE salary_tt
   IS
      TABLE OF employees.salary%TYPE
         INDEX BY BINARY_INTEGER;
   salaries       salary_tt;
BEGIN
   OPEN employees_cur;
   LOOP
      FETCH employees_cur
         BULK COLLECT INTO employee_ids, salaries
         LIMIT fetch_limit_in;
      FOR indx IN 1 .. employee_ids.COUNT
      LOOP
         salaries (indx) := compensation_rules.adjusted_compensation (
            employee_id_in    => employee_ids (indx)
          , pct_increase_in   => pct_increase_in
         );
      END LOOP;
      FORALL indx IN 1 .. employee_ids.COUNT
      SAVE EXCEPTIONS
         UPDATE employees
            SET salary = salaries (indx)
          WHERE employee_id = employee_ids (indx);
      EXIT WHEN employees_cur%NOTFOUND;
   END LOOP;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. sql%BULK_EXCEPTIONS.COUNT
      LOOP
         q$error_manager.register_error (
            error_code_in   => sql%BULK_EXCEPTIONS (indx).ERROR_CODE
          , name1_in        => 'EMPLOYEEE_ID', value1_in        =>  employee_ids (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY' , value3_in        =>  salaries (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
         );
      END LOOP;
END change_salary_for;

1. Declare a named exception for the ORA-24381 exception that FORALL will raise if at least one error occurs in that statement; declare the cursor that identifies the desired data, as well as collections to hold the data that will be retrieved “in bulk.”
2. Open the cursor, and then within a loop, fetch up to FETCH_LIMIT_IN rows at a time with a FETCH . . . BULK COLLECT statement.
3. For each of those rows, adjust the salary that is now in the collection named “salaries.”
4. Use the FORALL statement to update all the rows just fetched with the new salary. Include the SAVE EXCEPTIONS clause to suppress exceptions at the statement level.
5. Terminate the loop when all rows have been fetched.

Now let’s take a closer look at the exception section. When I include the SAVE EXCEPTIONS clause in a FORALL statement, my program works as follows:

• If an insert, update, delete, or merge statement fails, Oracle Database will roll back changes to any rows made by that statement. It will then save the index in the binding array on which the error occurred and the error code in the SQL%BULK_EXCEPTIONS pseudo-collection of records and continue to the next statement.
• When all statements have been executed, if at least one error was encountered, Oracle Database will raise the ORA-24381 exception.
• In the exception section, I iterate through all the elements in the SQL%BULK_EXCEPTIONS collection and use the ERROR_INDEX and ERROR_CODE fields to log the error.

The third way to continue past an exception in an insert, update, delete, or merge statement is to use the DML error logging feature, by including a LOG ERRORS clause in the statement. LOG ERRORS can be used within or independent of FORALL and suppresses exceptions at the row level, instead writing error information out to an error log table.

To use this feature, I first ask Oracle Database to create an error log table for the employees table:

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

I will now find a table with the following name and structure in my schema:

TABLE ERR$_EMPLOYEES
(
ORA_ERR_NUMBER$  NUMBER,
ORA_ERR_MESG$      VARCHAR2(2000 BYTE),
ORA_ERR_ROWID$    UROWID(4000),
ORA_ ERR_OPTYP$    VARCHAR2(2 BYTE),
ORA_ERR_TAG$         VARCHAR2(2000 BYTE),
EMPLOYEE_ID           VARCHAR2(4000 BYTE),
FIRST_NAME            VARCHAR2(4000 BYTE),
LAST_NAME             VARCHAR2(4000 BYTE),
EMAIL                    VARCHAR2(4000 BYTE),
PHONE_NUMBER       VARCHAR2(4000 BYTE),
HIRE_DATE              VARCHAR2(4000 BYTE),
JOB_ID                   VARCHAR2(4000 BYTE),
SALARY                   VARCHAR2(4000 BYTE),
COMMISSION_PCT     VARCHAR2(4000 BYTE),
MANAGER_ID           VARCHAR2(4000 BYTE),
DEPARTMENT_ID       VARCHAR2(4000 BYTE)
)

Note that this table contains five generic columns for the error information (error number, error message, rowid, and so on) and a VARCHAR2(4000) column for each column of a supported datatype in the EMPLOYEES “DML table.”When I add LOG ERRORS to the end of my insert, update, delete, or merge statement, Oracle Database will automatically insert a row into this table for each exception raised by the statement.

In my third implementation of the CHANGE_SALARY_FOR procedure, shown in Listing 3, I start with the BULK COLLECT . . . FORALL implementation and make these changes:

Code Listing 3: CHANGE_SALARY_FOR using FORALL and LOG ERRORS

PROCEDURE change_salary_for (
   dept_in             IN employees.department_id%TYPE
 , pct_increase_in  IN NUMBER
 , fetch_limit_in    IN PLS_INTEGER
)
IS
   bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   CURSOR employees_cur
   IS
      SELECT employee_id, salary FROM employees WHERE department_id = dept_in;
   TYPE employee_tt
   IS
      TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;
   employee_ids   employee_tt;
   TYPE salary_tt
   IS
      TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;
   salaries       salary_tt;
   PROCEDURE log_errors
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      FOR error_rec IN (SELECT * FROM err$_employees)
      LOOP
         q$error_manager.register_error (
            error_code_in   => error_rec.ora_err_number$
          , name1_in        => 'EMPLOYEEE_ID'
          , value1_in        => error_rec.employee_id
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY'
          , value3_in        => error_rec.salary
         );
      END LOOP;
      DELETE FROM err$_employees;
      COMMIT;
   END log_errors;
BEGIN
   OPEN employees_cur;
   LOOP
      FETCH employees_cur
         BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;
      FOR indx IN 1 .. employee_ids.COUNT
      LOOP
         salaries (indx) := compensation_rules.adjusted_compensation (
            employee_id_in    => employee_ids (indx)
          , pct_increase_in    => pct_increase_in
         );
      END LOOP;
      FORALL indx IN 1 .. employee_ids.COUNT()UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
         LOG ERRORS REJECT LIMIT UNLIMITED;
      log_errors ();
      EXIT WHEN employee_ids.COUNT() < fetch_limit_in;
   END LOOP;
END change_salary_for;

• Remove the exception section in its entirety. With LOG ERRORS, exceptions are not raised, either during or after the FORALL statement; instead, data is written to the log table.
• Create a log_errors procedure to retrieve the contents of the err$_ employees table; log an error, using my application’s standard logging mechanism for each row in err$_employees; and then delete all the errors from that table.
• Change the FORALL statement, by removing the SAVE EXCEPTIONS clause and adding LOG ERRORS REJECT LIMIT UNLIMITED. With this clause, no matter how many exceptions are raised, each exception will be recorded in the err$_employees table.
• Call the log_errors procedure immediately after the FORALL statement.

Listing 3 demonstrates that LOG ERRORS enables you to continue past an exception—in this case, by ensuring that an exception is never even raised in your program. You just need to be very sure to check the contents of that error log table after the insert, update, delete, or merge statement has completed.

As is so often the case in programming, you have a choice of implementations to meet your requirements. All three enable you to continue past exceptions. How do you decide which approach is best for your situation?

The nested block approach offers one key advantage: the code is concise and simple, and it is easy to understand and maintain. You don’t have to declare collection types to perform the bulk SQL operations and write all that extra code to deal with the “bulk”errors you might encounter. It is, unfortunately, also the slowest implementation.

So if you know you will be dealing with a very small amount of data (rows numbering in, say, the dozens or hundreds) or if performance is not a criterion for your program, use the nested block with its own exception section. I suggest, however, that making either of those assumptions about your program is dangerous!

Now let’s move on to SAVE EXCEPTIONS versus LOG ERRORS (DML error logging). In other words, you have decided you need to use BULK COLLECT and FORALL, and you need to continue past exceptions. Which should you use?

The distinctive features of SAVE EXCEPTIONS are as follows:

• Error codes are recorded in the SQL%BULK_EXCEPTIONS pseudocollection of records and automatically erased by Oracle Database. You do not have to maintain the contents of the persistent database error log table.
• Exceptions are suppressed at the statement level, so if a statement’s change of one row raises an error, changes to other rows already made by that statement are also rolled back. If this is what you want or need, then using SAVE EXCEPTIONS is the way to go.

The distinctive features of DML error logging are as follows:

• Error information is recorded in a persistent database table, which you must manually clean up to ensure that errors from subsequent statements are not mixed up with earlier errors. Remember that this error log table can be populated by different users, all performing operations on the DML table.
• Oracle Database records information in the error log table that is much more detailed than in the SQL%BULK_EXCEPTIONS pseudocollection. You can, for example, retrieve the error message, rowid, and table column values.
• Exceptions are suppressed at the row level. Even if a statement’s change of one row raises an error, changes to other rows already made by that statement are not rolled back, and Oracle Database continues to change the remaining rows identified by the statement. If this is the level of transaction processing granularity you need, using LOG ERRORS is the only way to do it.

I would expect that, in most situations, DML error logging with the DBMS_ERRLOG package and LOG ERRORS is the optimal solution.

READ more PL/SQL Practices
blogs.oracle.com/oraclemagazine/steven-feuerstein
oracle.com/technetwork/articles/plsql

DOWNLOAD Oracle Database 11g

DISCUSS PL/SQL