Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


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

  1. 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.

  2. RAISE exception

    RAISE statement to raised defined exception name and control transfer to a EXCEPTION block.

    RAISE user_define_exception_name;
  3. 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 Code

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;
/

Example Result

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

PL/SQL procedure successfully operation.