EXCEPTION_INIT is a compile-time command or pragma used to associate a exception name with an internal error code.EXCEPTION_INIT instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number.
Once you have made that association, you can then raise that exception by name and write an explicit WHEN handler that traps the error.
The pragma EXCEPTION_INIT must appear in the declaration section of a block; the exception named must have already been defined in that same block, an enclosing block, or a package specification.
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_number);
The error number can be any integer value with these constraints :-
• It cannot be –1403 (ORA-01403: no data found).
• It cannot be 0 or any positive number besides 100.
• It cannot be a negative number less than –1000000.
SQL> DECLARE
2 no_data_found EXCEPTION;
3 PRAGMA EXCEPTION_INIT(no_data_found,-20138);
4 BEGIN
5 RAISE no_data_found;
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE(SQLCODE||' - '||SQLERRM);
9 END;
10 /
--OUTPUT :::
-20138 - ORA-20138:
SQL> DECLARE
2 bal_too_low EXCEPTION;
3 PRAGMA EXCEPTION_INIT(bal_too_low,-1403);
4 BEGIN
5 RAISE bal_too_low;
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE(SQLCODE||' - '||SQLERRM);
9 END;
10 /
--OUTPUT :::
bal_too_low EXCEPTION;
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
SQL> DECLARE
2 bal_too_low EXCEPTION;
3 PRAGMA EXCEPTION_INIT(bal_too_low,6);
4 BEGIN
5 RAISE bal_too_low;
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE(SQLCODE||' - '||SQLERRM);
9 END;
10 /
--OUTPUT :::
bal_too_low EXCEPTION;
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00701: illegal ORACLE error number 6 for PRAGMA EXCEPTION_INIT
SQL> DECLARE
2 bal_too_low EXCEPTION;
3 PRAGMA EXCEPTION_INIT(bal_too_low,100);
4 BEGIN
5 RAISE bal_too_low;
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE(SQLCODE||' - '||SQLERRM);
9 END;
10 /
--OUTPUT :::
100 - ORA-01403: no data found
SQL> DECLARE
2 bal_too_low EXCEPTION;
3 PRAGMA EXCEPTION_INIT(bal_too_low,1000001);
4 BEGIN
5 RAISE bal_too_low;
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE(SQLCODE||' - '||SQLERRM);
9 END;
10 /
--OUTPUT :::
bal_too_low EXCEPTION;
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00701: illegal ORACLE error number 1000001 for PRAGMA EXCEPTION_INIT
