As discussed in the introduction of PL/SQL Exception Handling, there is three ways of declaring user-define exceptions. Among those three ways we have already learnt the first two ways which are declaring user-define exception using

  1. Raise Statement and
  2. Raise_Application_Error procedure

The only way which is left to be discussed now is declaring user define exceptions using PRAGMA EXCEPTION_INIT function in Oracle Database.

Thus in this PL/SQL tutorial we will learn how to declare PL/SQL user-define exception in Oracle Database by using PRAGMA EXCEPTION_INIT function.

What is PRAGMA EXCEPTION_INIT?

Pragma Exception_Init is a two part statement where first part is made up of keyword PRAGMA and second part is the Exception_Init call.

PRAGMA Keyword

A pragma is a compiler directive which indicates that the Statements followed by keyword PRAGMA is a compiler directive statement this means that the statement will be processed at the compile time & not at the runtime.

PRAGMA Exception_Init

Exception_init helps you in associating an exception name with an Oracle error number. In other words we can say that using Exception_Init you can name the exception.

Why name the exception?

Yes, there is a way of declaring user-define exception without the name and that is by using Raise_Exception_Error procedure. This indeed is a simple and easy way but as we learnt in the last tutorial that to handle exceptions without name we use OTHERS exception handler.

Now think that in your project you have multiple exceptions and that too without name. In order to handle all those exceptions you have a single exception handler with name OTHERS. In this case on the occurrence of an exception condition the compiler will display the error stack produced by the OTHER handler.

Can you imagine how difficult it will be to trace that part of your project which is causing the error. In order to trace that part you need to go through each & every line of your code. This will be mere waste of time.

You can save all those time wasting efforts just by naming the exception, that way you can design an exception handler specific to the name of your exception which will be easily traceable. This is the advantage of naming the exception.

Syntax of Pragma Exception_Init.

PRAGMA EXCEPTION_INIT (exception_name, error_number);

Example: Declare User-define exception using Pragma Exception_Init

DECLARE
  ex_age    EXCEPTION;
  age       NUMBER    := 17;
  PRAGMA EXCEPTION_INIT(ex_age, -20008);
BEGIN
  IF age<18 THEN
    RAISE_APPLICATION_ERROR(-20008, 'You should be 18 or above for the drinks!');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Sure! What would you like to have?');
  
  EXCEPTION WHEN ex_age THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);   
END;
/

I have explained this example in my Video Tutorial on my YouTube channel in detail I would request you to check out the tutorial there.

Why use PRAGMA EXCEPTION_INIT with RAISE_APPLICATION_ERROR?

Though it is not mandatory to use PRAGMA EXCEPTION_INIT with RAISE_APPLICATION_ERROR procedure however it is more of a personal preference than a programming rule. If you want to print an error message with an error number like the Oracle’s standard way of showing an error then it is the best practice to use PRAGMA EXCEPTION_INIT with RAISE_APPLICATION_ERROR procedure.

how to declare user defined exception using pragma exception init in oracle database by manish sharma

But if you just want to print the error message & not the error number then you can use PRAGMAEXCEPTION_INIT with RAISE statement.  

That is the PL/SQL tutorial on how to declare user-define exception in Oracle Database using PRAGMA EXCEPTION_INIT. Hope you learnt something new. If you find this tutorial informative then do share on your social media. Thanks & have a great day!

RELATED ARTICLESMORE FROM AUTHOR