PL/SQL User Defined Exception

PL/SQL user defined exception to make your own exception. PL/SQL give you control to make your own exception base on oracle rules. User define exception must be declare yourself and RAISE statement to raise explicitly.

How to Define Exception

Declare exception

You must have to declare user define exception name in DECLARE block.
```
user_define_exception_name EXCEPTION;
```
Exception and Variable both are same way declaring but exception use for store error condition not a storage item.
RAISE exception

RAISE statement to raised defined exception name and control transfer to a EXCEPTION block.
```
RAISE user_define_exception_name;
```
Implement exception condition

In PL/SQL EXCEPTION block add WHEN condition to implement user define action.
```
WHEN user_define_exception_name THEN
    User defined statement (action) will be taken;
```

Syntax

Check this user defined exception syntax,

DECLARE
    user_define_exception_name EXCEPTION;
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_exp.sql

SQL>edit user_exp
DECLARE
    myex EXCEPTION;
    i NUMBER;
BEGIN
    FOR i IN (SELECT * FROM enum) LOOP
        IF i.eno = 3 THEN
            RAISE myex;
        END IF;
    END LOOP;
EXCEPTION
    WHEN myex THEN
        dbms_output.put.line('Employee number already exist in enum table.');
END;
/

Result

SQL>@user_exp
Employee number already exist in enum table.

PL/SQL procedure successfully operation.