(9) Exception Handling

Handling exceptions
  • Exceptions can be either an Oracle error or a user-defined error.
  • Oracle errors include predefined exceptions enclosed in the STANDARD package.
  • There are also unamed Oracle errors. It is possible to associate a name with an unamed Oracle error using the Pragma EXCEPTION_INIT
  • User-defined exceptions are raised using
    (a) the RAISE statement, or
    (b) through calls to RAISE_APPLICATION_ERROR procedure.

Exception Handling block syntax:

WHEN {predefined_exception | user_defined_exception | OTHERS} THEN

  WHEN ex_name_1 THEN statements_1               -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                  -- Exception handler

  • Catches all exceptions not explicitly handled in the previous exception-handling parts of the block.
  • Recommendation: The last statement in the OTHERS exception handler should be either
    RAISE or an call of the RAISE_APPLICATION_ERROR procedure.
  • If you do not follow this practice and PL/SQL warnings are enabled, you get PLW-06009.
  • WHEN OTHERS is optional. It can appear only once, as the last exception handler in the exception-handling part of the block.

Exception Propagation
  • Handlers in the Exception section catch exceptions raised in the EXECUTABLE section of the block.
  • Exception raised in Declaration section
    • Errors in the Declaration section are usually assignment errors, but can still be caught, not locally, but in the enclosing block.
    • Exception raised in a Declaration section propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block).
    • Therefore, the exception handler must be in an enclosing or invoking block, not in the same block as the declaration.
    • It is probably better coding practice to avoid assignment in the Declaration section altogether.
  • Exceptions raised in the Exception Section:
    • Exception raised in an Exception section propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block).
  • Exceptions in calls between subprograms

Handling exceptions:
  • Case 1: An exception handler cannot catch an exception raised in the exception section of the same block.
  • Such exceptions propagate to the outer block and can only be caught there or in further outer blocks.
set serveroutput on         
  A exception;
  B exception;
  raise a;
  when a then 
     dbms_output.put_line('Raising b from A handler.');     -- exception b raised here CANNOT be 
                                                            -- caught by a handler in the same block. 
     raise b;
  when b then 
     dbms_output.put_line('exception b caught');
                                                            -- Here the exception is not caught.
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
ORA-06510: PL/SQL: unhandled user-defined exception
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.

  • Case 2: The exception raised within an Exception section can only be caught in an outer block.
set serveroutput on         
DECLARE                                                           -- outer block
  A exception;
  B exception;
  begin                                                    -- inner block
    raise a;
    when a then 
       dbms_output.put_line('Raising b from A handler.');         -- exception b raised here is caught in the inner block 
       raise b;
    when b then 
       dbms_output.put_line('exception b caught in inner_block');
 when b then 
       dbms_output.put_line('exception B caught in out_block');
Anonymous block completed
Raising b from A handler.
exception B caught in out_block

Exception Categories:
Internally defined:
  • (ORA-n errors). automatically raised. Do not have names, unless explicitly given (user). Naming an ORA-n allows that error to be handled on its own WHEN section.Otherwise, you can handle them only with OTHERS exception handlers.

--- i.e. Naming an internal error for explict handling:
 -- (1) declare a name of "type" EXCEPTION.
 deadlock_detected EXCEPTION;
 -- (2) Associate the name with the internally defined error code
 PRAGMA EXCEPTION_INIT(Deadlock_detected, -60);
  WHEN deadlock_detected THEN

PL/SQL Predefined Exceptions
Exception Name Error Code

Predefined Exceptions:
  • An internally defined exception that PL/SQL has given a name (22 total). You can write exception handlers specifically for them.
  • They are defined in the SYS.STANDARD package

  • SQLCODE Function:
    • Returns the numeric code of the exception being handled
    • For predefined errors: Returns a negative number that maps to the Oracle predefined exceptions. (for NO_DATA_FOUND returns a positive #. (why?))
    • For User-defined errors: Returns a positive 1 or the error code associated with the exception by the EXCEPTION_INIT PRAGMA. (a valid number in the range of negative 20001 to negative 20999.)

  • SQLERRM Function
    • returns the error message associated with an error code
    • Returns max 512 bytes, which is the max length of an Oracle error message
    • DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause.
    • DBMS_UTILTY.FORMAT_ERROR_STACK returns the full error stack (up to 2kb)

  • DBMS_UTILITY Package
    • DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause.
    • DBMS_UTILTY.FORMAT_ERROR_STACK() returns the full error stack (up to 2kb)
    • DBMS_UTILTY.FORMAT_ERROR_BACKTRACE() lists the complete error stack, including the line number that generated the exception.
    • See example here

User-defined Exceptions:
  • Two-Step process:
  • 1. Declaration Section:
    Declare exception_name EXCEPTION;
  • 2. Declaration Section:
    Declare a PRAGMA to associate a number to the exception.
    • A PRAGMA is a compiler directive. You use a PRAGMA to direct the compiler to perform something differently. PL/SQL supports a number of PRAGMA directives.
    • You use the EXCEPTION_INIT directive to map an exception to an error code.
  • Executable Section: Explicitly raise exception using: RAISE or RAISE_APPLICATION_ERROR.
  • RAISE_APPLICATION_ERROR is defined in the DBMS_STANDARD package.
  • can only be invoked from a stored subprogram or method.
  • Syntax: RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

Raising a user-defined Exception (with RAISE):
create or replace procedure account_status (
  due_date date,
  today    date ) authid definer is
  -- declare user-defined exception.
  past_due exception;
  if due_date < today then 
    raise past_due;
  end if;
 WHEN past_due THEN
   dbms_output.put_line('Error: Account past due...');

SQL> set serveroutput on
SQL> begin
account_status('1-JUL-10', '2-AUG-10');

anonymous block completed
Error: Account past due...

Declaration Block Errors:
Runtime assignment errors in the declaration block are not captured by the local exception block.

-- dynamic assignments in the declaration section of a block
-- may lead to run-time errors that are not captured by the 
-- local EXCEPTION block. 
set serveroutput on
  emp_id  employees.employee_id%type := '&1';
  vsal    employees.salary%type;
  dbms_output.put_line('Dynamic value captured: '|| emp_id);
  select salary into vsal
  from employees
  where employee_id = emp_id;
  dbms_output.put_line('Emp '|| emp_id || '-- salary: '|| vsal);
 when others then 
   dbms_output.put_line('local exception caught');

-- case 1: Value entered matches the datatype.
--         Procedure executes successfully
Enter value for 1: 100
old   2:   emp_id  employees.employee_id%type := '&1';
new   2:   emp_id  employees.employee_id%type := '100';
Dynamic value captured: 100
Emp 100-- salary: 24000

PL/SQL procedure successfully completed.

-- case 2: Invalid value entered.
-- Exception occurs and is not caught by the local 
-- error handling section.
Enter value for 1: 1G0
old   2:   emp_id  employees.employee_id%type := '&1';
new   2:   emp_id  employees.employee_id%type := '1G0';
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

  • IMP: Stored programs (i.e. functions and procedures) behave similarly.
  • Procedures require wrapping their calls, but functions do not.
  • Thus, for functions called directly from SQL, if a declaration exception occurs, their local EXCEPTION section will not catch and an unhandled exception will result.
  • Make dynamic assignments in execution blocks because PL/SQL doesn’t catch dynamic assignment errors in local exception handlers.

Raising a user-defined Exception with RAISE_APPLICATION_ERROR:

-- create a stored procedure to insert new employee
-- the procedure raises an exception if the hire date is 
-- a future date.
create or replace procedure new_emp 
  (empid  employees.employee_id%type,
   lname  employees.last_name%type,
   fname  employees.first_name%type,
   dept   employees.department_id%type,
   jobid  employees.job_id%type,
   sal    employees.salary%type,
   hiredt employees.hire_date%type
   ) authid definer is
  if hiredt > sysdate then 
     raise_application_error(-20000, 'Future hire dates not allowed.');
     insert into employees 
       (employee_id, last_name, first_name, department_id,
        job_id, salary, hire_date)
     values(empid, lname, fname, dept, jobid, sal, hiredt);
  end if;
end new_emp;

Stored procedure created.

-- autonomous block invokes the procedure new_emp
-- it defines the INVALID_HIRE_DATE exception
-- and associate an error number with PRAGMA EXCEPTION_INIT
SQL> set serveroutput on
SQL> declare
  invalid_hire_date exception;
  pragma exception_init(invalid_hire_date, -20000);
  new_emp(200, 'Marty', 'Moe', 30, 'SA_REP', 3000, '1-JAN-14');
  when invalid_hire_date then

ORA-20000: Future hire dates not allowed.

PL/SQL procedure successfully completed.

Handling Exceptions: Guidelines
  • (1) Use error-checking code and exception handlers.
  • error-checking code: check for possible bad input data. (i.e. nulls when they should not be there, more rows than you expected, strings when you should have numbers, etc..)
  • (2) Add exception handlers wherever errors can occur. (i.e. arithmetic calculations, string manipulation, and database operations. Also disk storage, memory hardware failure, etc).
  • (3) Design your programs to work when the database is not in the state you expect. (i.e. a table might have a column added. don't do select *.... You can also declare variables with %TYPE and %ROWTYPE)
  • (4) Use named exceptions instead of using OTHERS exception handlers.
  • (5) Have your exception handlers output debugging information. You can use autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) to commit your debug info).
  • (6)For each exception handler, decide whether to commit, rollback or escalate.
  • (7)Make sure to leave the db in a consistent state.
  • (5)Include WHEN OTHERS THEN to avoid unhandled exceptions.
  • (5) Make sure the last statement in the OTHERS handler either RAISE or an invocation of the RAISE_APPLICATION_ERROR procedure. (or you´ll get PLW-06009.)

Exception Stack
  • Exception stack: the sequencing of errors from the triggering event to the calling block of
  • When a failure occurs: (a) An exception is raised in the execution block and the the code in the local exception block executed. (b) If the block is nested or a referenced PL/SQL block, it (a.1) runs the local exception handler and then (a.2) runs the calling program unit’s exception handler, and (a.3, a.4, ...) It continues running available exception blocks or returning errors to the error stack until it returns control to the outermost PL/SQL block.
  • Beginning in Oracle 10g, you can use an exception block and the DBMS_UTILITY package to get line number and error codes.

Exception handling in Triggers

Exceptions in Bulk Statements (FORALL)

No comments:

Post a Comment