11.6 Redeclared Predefined Exceptions
Oracle recommends against redeclaring predefined exceptions—that is, declaring a user-defined exception name that is a predefined exception name. (For a list of predefined exception names, see Table 11-3.)
If you redeclare a predefined exception, your local declaration overrides the global declaration in package STANDARD
. Exception handlers written for the globally declared exception become unable to handle it—unless you qualify its name with the package name STANDARD
.
Example 11-9 shows this.
Example 11-9 Redeclared Predefined Identifier
DROP TABLE t; CREATE TABLE t (c NUMBER);
In the following block, the INSERT
statement implicitly raises the predefined exception INVALID_NUMBER
, which the exception handler handles.
DECLARE default_number NUMBER := 0; BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
Substituting default value for invalid number.
The following block redeclares the predefined exception INVALID_NUMBER
. When the INSERT
statement implicitly raises the predefined exception INVALID_NUMBER
, the exception handler does not handle it.
DECLARE default_number NUMBER := 0; i NUMBER := 5; invalid_number EXCEPTION; -- redeclare predefined exception BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
The exception handler in the preceding block handles the predefined exception INVALID_NUMBER
if you qualify the exception name in the exception handler:
DECLARE default_number NUMBER := 0; i NUMBER := 5; invalid_number EXCEPTION; -- redeclare predefined exception BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN STANDARD.INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
Substituting default value for invalid number.