PL/SQL User Named Exception

PL/SQL user named exception. you can define your own error message and error number using Pragma EXCEPTION_INIT or RAISE_APPLICATION_ERROR function.

PL/SQL pragma EXCEPTION_INIT

pragma EXCEPTION_INIT: Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument,

  1. exception_name
  2. error_number

You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program.

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

exception_name and error_number define on yourself, where exception_name is character string up to 2048 bytes suppot and error_number is a negative integer range from -20000 to -20999.

Syntax

DECLARE
    user_define_exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number);
BEGIN
    statement(s);
    IF condition THEN
        RAISE user_define_exception_name;
    END IF;
EXCEPTION
    WHEN user_define_exception_name THEN
        User defined statement (action) will be taken;
END;

Example

user-named_exp.sql
SQL>edit user-named_exp
DECLARE
    myex EXCEPTION;
    PRAGMA EXCEPTION_INIT(myex,-20015); 
    n NUMBER := &n;
BEGIN
    FOR i IN 1..n LOOP
        dbms_output.put.line(i);
        IF i=n THEN
            RAISE myex;
        END IF;
    END LOOP;
EXCEPTION
    WHEN myex THEN
        dbms_output.put.line('loop finish');
END;
/

Result

SQL>@user-named_exp
n number &n= 5
1
2
3
4
5
loop finish

PL/SQL procedure successfully operation.

PL/SQL RAISE_APPLICATION_ERROR

In PL/SQL RAISE_APPLICATION_ERROR function use to assign exception name and exception error code.

Syntax

raise_application_error(error_number, error_message);

Example

raise_app_error.sql
SQL>edit user-named_exp
DECLARE
    myex EXCEPTION;
    n NUMBER := &n;
BEGIN
    FOR i IN 1..n LOOP
        dbms_output.put.line(i);
        IF i=n THEN
            RAISE myex;
        END IF;
    END LOOP;
EXCEPTION
    WHEN myex THEN
        RAISE_APPLICATION_ERROR(-20015, 'loop finish');
END;
/

Result

SQL>@raise_app_error
n number &n= 5
1
2
3
4
5
ORA-20015: loop finish

PL/SQL procedure successfully operation.

When RAISE_APPLICATION_ERROR execute it's return error message and error code looking same as oracle built-in error.