Understanding PRAGMA EXCEPTION_INIT In Oracle Database

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