PRAGMA EXCEPTION_INIT in ORACLE

We have two types of exceptions 1: user defined 2: pre defined

The EXCEPTION_INIT pragma associates a user-defined exception name with an error code.

Example1:

DECLARE

    deadlock_detected EXCEPTION;

    PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

BEGIN

    ...

EXCEPTION

    WHEN deadlock_detected THEN

    ...

END;

Pre Defined Exceptions:

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD.

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

Example2: Pre defined exception example

declare

    v_devide int := 0;

    v_sum int;

begin

    select sum(sal)/v_devide into v_sum

        from emp;

exception

    when zero_divide then

        dbms_output.put_line ('Error 200-> zero_devide error' || sqlerrm  );

    when others then

        dbms_output.put_line ('Error 100->' || sqlerrm );

end;

output: Error 200-> zero_devide errorORA-01476: divisor is equal to zero

Example3: User & Pre defined exception for same error number gives PLS-00484 error

Let’s take the same example as in example-2 (above) and extend it by adding user defined exception for the error -1476.

Error -1476 is already assigned to ZERO_DIVIDE pre defined exception and if we define user define exception for the same error number then at the time of code block execution we will see PLS-00484: redundant exceptions error.

declare

    v_devide int := 0;

    v_sum int;

    my_zero_devide exception;

    pragma exception_init (my_zero_devide, -1476 );

begin

    select sum(sal)/v_devide into v_sum

        from emp;

exception

    when my_zero_devide then

        dbms_output.put_line ('Error 300-> zero_devide error' || sqlerrm  );

    when zero_divide then

        dbms_output.put_line ('Error 200-> zero_devide error' || sqlerrm  );

    when others then

        dbms_output.put_line ('Error 100->' || sqlerrm );

end;

Error at line 1

ORA-06550: line 10, column 5:

PLS-00484: redundant exceptions 'ZERO_DIVIDE' and 'MY_ZERO_DEVIDE' must appear in same exception handler

ORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminated

Example4: re-declaring pre defined exceptions

Let’s take the same code block above but this time we are not going to associate user defined exception with error number.

declare

    v_devide int := 0;

    v_sum int;

    zero_devide exception;

begin

    select sum(sal)/v_devide into v_sum

        from emp;

exception

    when zero_devide then

        dbms_output.put_line ('Error 300-> zero_devide error' || sqlerrm  );

end;

The above code block gives following error and this error is unhandled in the above exception block.

Error at line 1

ORA-01476: divisor is equal to zero

ORA-06512: at line 6

Now, handle the error by using STANDARD.ZERO_DIVIDE pre defined exception

declare

    v_devide int := 0;

    v_sum int;

    zero_devide exception;

begin

    select sum(sal)/v_devide into v_sum

        from emp;

exception

    when zero_devide then

        dbms_output.put_line ('Error 300-> zero_devide error' || sqlerrm  );

    when standard.zero_divide then

        dbms_output.put_line ('Error 200-> zero_devide error' || sqlerrm  );

    when others then

      dbms_output.put_line ('Error 100->' || sqlerrm );

end;

output:

Error 200-> zero_devide errorORA-01476: divisor is equal to zero

Example5: Raising User-Defined Exception with RAISE_APPLICATION_ERROR

declare

    v_devide int := 0;

    v_sum int;

    my_zero_devide exception;

    pragma exception_init ( my_zero_devide, -20000);

begin

    begin

        select sum(sal)/v_devide into v_sum     from emp;

    exception

        when others then

            raise_application_error (-20000, '  Error from Inner Exception');

    end;

exception

    when my_zero_devide then

        dbms_output.put_line ('Error 300-> zero_devide error' || sqlerrm  );

    when standard.zero_divide then

        dbms_output.put_line ('Error 200-> zero_devide error' || sqlerrm  );

    when others then

      dbms_output.put_line ('Error 100->' || sqlerrm );

end;

The raise_application_error throws error code as -20000 when the error happen inside the inner block while executing the select statement.

As we have a user defined exception for the error code -20000, this error will be handled by exception my_zero_devide.