Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


User Named Exception PRAGMA EXCEPTION_INIT, RAISE_APPLICATION_ERROR


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 Code

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

Example 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. Define RAISE_APPLICATION_ERROR function syntax,

raise_application_error(error_number, error_message);

Example Code

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

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