Oracle Magazine - March/April 2012

Code Listing 1: Exception handling section inserting into log table

EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_code INTEGER := SQLCODE;
BEGIN
INSERT INTO error_log (error_code
, error_message
, backtrace
, callstack
, created_on
, created_by)

VALUES (l_code

, sys.DBMS_UTILITY.format_error_stack , sys.DBMS_UTILITY.format_error_backtrace , sys.DBMS_UTILITY.format_call_stack , SYSDATE , USER);

RAISE; END;

Code Listing 2: Exception handling procedure inserting into log table

CREATE OR REPLACE PROCEDURE record_error
IS
BEGIN
INSERT INTO error_log (error_code
, error_message
, backtrace
, callstack
, created_on
, created_by)

VALUES (l_code , SQLERRM , sys.DBMS_UTILITY.format_error_backtrace , sys.DBMS_UTILITY.format_call_stack , SYSDATE , USER);

END;

Code Listing 3: Exception handling procedure as autonomous transaction with COMMIT

CREATE OR REPLACE PROCEDURE record_error
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log (error_code
, error_message
, backtrace
, callstack
, created_on
, created_by)

VALUES (l_code , SQLERRM , sys.DBMS_UTILITY.format_error_backtrace , sys.DBMS_UTILITY.format_call_stack , SYSDATE , USER);

COMMIT;

END;

unless you are certain that no error will be raised. You can, instead, assign the value in the executable section, and then the exception handler can trap and record the error:

DECLARE l_number NUMBER ( 1);

BEGIN l_number := 100;

statement1; ... statementN;

EXCEPTION
WHEN OTHERS
END;

EXCEP TIONS AND ROLLBACKS

Unhandled exceptions do not automatically result in the rollback of outstanding changes in a session. Indeed, unless you explicitly code a ROLLBACK statement into your exception section or the exception propagates unhandled to the host environment, no rollback will occur. Let’s look at an example.

Suppose I write a block of code that performs two data manipulation language (DML) operations:

1. Remove all employees from the Employees table who are in department 20.

2. Give a raise to all remaining employees by multiplying their current salary by 200.

That is very generous, but the constraint on the salary column is defined as NUMBER( 8, 2). The salary of some employees is already so large that the new salary amount will violate this constraint, leading Oracle Database to raise the “ORA-01438: value larger than specified precision allowed for this column” error.