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.
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;Nested Block with Exception Section
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.
FORALL with SAVE EXCEPTIONSThat “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;
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:
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;
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.
Which Approach Is Best?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:
The distinctive features of DML error logging are as follows:
I would expect that, in most situations, DML error logging with the DBMS_ERRLOG package and LOG ERRORS is the optimal solution.
Next StepsREAD more PL/SQL Practices
blogs.oracle.com/oraclemagazine/steven-feuerstein
oracle.com/technetwork/articles/plsql
DOWNLOAD Oracle Database 11g
DISCUSS PL/SQL
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.