General
==============================
PL/SQL Exception handling by example.
Index
==============================
Catch predefined Exception
Catch non-predefined specific ORA-999 Exception
Catch application Exception
Raise new Application Exception
==============================
Catch pre-defined Exception
==============================
There are some ~20 predefined Oracle Exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc.
Example:
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
==============================
Catch non-predefined specific ORA-999 Exception
==============================
Option A - with PRAGMA EXCEPTION_INIT
For Anonymous block/Function/Procedure - PRAGMA EXCEPTION_INIT should be under DECLARE section.
For Package - PRAGMA EXCEPTION_INIT should be in Package Specification.
Example:
DECLARE
my_deadlock_ora_exp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deadlock_ora_exp, -60); --catch ORA-00060
PRAGMA EXCEPTION_INIT(e_resource_busy_ora_exp, -54); --catch ORA-00054
PRAGMA EXCEPTION_INIT(e_resource_busy_ora_exp, -54); --catch ORA-00054
BEGIN
... -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN e_deadlock_ora_exp THEN
-- handle ORA-00060
WHEN e_resource_busy_ora_exp THEN
-- handle ORA-00054
END;
Option B - without PRAGMA EXCEPTION_INIT
Example:
DECLARE
my_deadlock_ora_exp NUMBER := -60;
BEGIN
... -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = my_deadlock_ora_exp THEN
END IF;
END;
==============================
Raise new Application Exception
==============================
General syntax to raise application exceptions:
RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]);
Where
error_number - Any number in the range -20000 .. -20999
message - Free text, up to 2048 bytes long
Third Parameter is optional
If TRUE, the error is placed on the stack of previous errors.
If FALSE (the default), the error replaces all previous errors.
RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.
==============================
Catch application Exception
==============================
DECLARE
my_exception_exp EXCEPTION;
C_APP_EXCEPTION_NUM CONSTANT NUMBER := -20100;
v_msg_text VARCHAR2(2048);
C_APP_EXCEPTION_NUM CONSTANT NUMBER := -20100;
v_msg_text VARCHAR2(2048);
BEGIN
IF ... THEN
RAISE my_exception_exp;
END IF;
EXCEPTION
WHEN my_exception_exp THEN
RAISE_APPLICATION_ERROR(C_APP_EXCEPTION_NUM,v_msg_text);
END;
identifier "DBMS_LOCK" must be declared
GRANT EXECUTE ON SYS.DBMS_LOCK to MY_USER;
No comments:
Post a Comment