- 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
exception_handling_statement;
[RETURN | EXIT ];
---i.e.---
EXCEPTION
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
END;
[ WHEN OTHERS ]
- 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
- 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
declare
A exception;
B exception;
begin
raise a;
exception
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');
end;
/
-- 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
begin -- inner block
raise a;
exception
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');
end;
EXCEPTION
when b then
dbms_output.put_line('exception B caught in out_block');
END;
/
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:
DECLARE
-- (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);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
...
END;
| PL/SQL Predefined Exceptions |
|---|
| Exception Name | Error Code |
ACCESS_INTO_NULL | -6530 |
CASE_NOT_FOUND | -6592 |
COLLECTION_IS_NULL | -6531 |
CURSOR_ALREADY_OPEN | -6511 |
DUP_VAL_ON_INDEX | -1 |
INVALID_CURSOR | -1001 |
INVALID_NUMBER | -1722 |
LOGIN_DENIED | -1017 |
NO_DATA_FOUND | +100 |
NO_DATA_NEEDED | -6548 |
NOT_LOGGED_ON | -1012 |
PROGRAM_ERROR | -6501 |
ROWTYPE_MISMATCH | -6504 |
SELF_IS_NULL | -30625 |
STORAGE_ERROR | -6500 |
SUBSCRIPT_BEYOND_COUNT | -6533 |
SUBSCRIPT_OUTSIDE_LIMIT | -6532 |
SYS_INVALID_ROWID | -1410 |
TIMEOUT_ON_RESOURCE | -51 |
TOO_MANY_ROWS | -1422 |
VALUE_ERROR | -6502 |
ZERO_DIVIDE | -1476 |
- 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 PackageDBMS_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;
begin
if due_date < today then
raise past_due;
end if;
exception
WHEN past_due THEN
dbms_output.put_line('Error: Account past due...');
end;
SQL> set serveroutput on
SQL> begin
account_status('1-JUL-10', '2-AUG-10');
end;
/
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
declare
emp_id employees.employee_id%type := '&1';
vsal employees.salary%type;
begin
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);
exception
when others then
dbms_output.put_line('local exception caught');
end;
-- 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';
declare
*
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
begin
if hiredt > sysdate then
raise_application_error(-20000, 'Future hire dates not allowed.');
else
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);
begin
new_emp(200, 'Marty', 'Moe', 30, 'SA_REP', 3000, '1-JAN-14');
exception
when invalid_hire_date then
dbms_output.put_line(to_char(sqlerrm(-20000)));
end;
/
ORA-20000: Future hire dates not allowed.
PL/SQL procedure successfully completed.
SQL>
- (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: the sequencing of errors from the triggering event to the calling block of
code.
- 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.
