In the Introduction to PL/SQL Exceptions we learnt that there are three ways of declaring user-define exceptions in Oracle Database. In this tutorial we are going to explore the first way and learn how to declare user-define exception using a variable of Exception datatype.

Declaring a user-define exception using Exception variable is a three step process. These three steps are –

  1. Declare a variable of exception datatype – This variable is going to take the entire burden on its shoulders.
  2. Raise the Exception – This is the part where you tell the compiler about the condition which will trigger the exception.
  3. Handle the exception – This is the last section where you specify what will happen when the error which you raised will trigger.

In this PL/SQL tutorial I am going to explain to you each of these three steps with the help of a PL/SQL code.  

For the demonstration purpose I will write a code which will check whether the divisor is zero or not in the division operation. If it is zero then an error will occur and will be displayed to the user otherwise an actual value which is the result of the division arithmetic will be returned on the output screen.

Step 1: Declare a variable of Exception datatype

By Exception variable I mean a variable with Exception datatype. Like any other PL/SQL variable you can declare an Exception variable in declaration section of the anonymous as well as named PL/SQL block.  This exception variable will then work as user-define exception for your code.

DECLARE
  var_dividend NUMBER := 24;
  var_divisor NUMBER := 0;
  var_result NUMBER;
  ex_DivZero EXCEPTION;

In this declaration section we have 4 variables. Among these 4 variables first 3 are normal Number datatype variables and the 4th one which is ex_DivZero is the special EXCEPTION datatype variable. This variable will become our User-Define Exception for this program.

Step 2: Raise the Exception

The next step after declaring an Exception variable is to raise the exception. To raise the exception in PL/SQL we use Raise statement.

Raise statement is a special kind of PL/SQL statement which changes the normal flow of execution of the code. As soon as compiler comes across a raise condition it transfers the control over to the exception handler.

BEGIN
  IF var_divisor = 0 THEN
    RAISE ex_DivZero;
  END IF;

Here raise condition is accompanied with the IF-THEN condition. With the help of this we can avoid unwanted switches during the control flow of the program. Using If Condition we are making sure that this error will come into action only when the divisor is equal to 0.

var_result := var_dividend/var_divisor;
  DBMS_OUTPUT.PUT_LINE('Result = ' ||var_result);

After writing the logic for raising the error you can write your other executable statements of the code just like we did here. After the Raise statement we are performing the arithmetic of division operation and storing the result into the variable var_result, as well as displaying it back as the output using the DBMS OUTPUT statement. 

Step 3: Handle the exception

That is the main section of the code. Here we write the logic for our user-define exception and tell the compiler what it should do if and when that error occurs.

EXCEPTION WHEN ex_DivZero THEN
    DBMS_OUTPUT.PUT_LINE('Error Error - Your Divisor is Zero');
END;
/

Here we have the exception handler for the variable ex_DivZero. In the exception handling section we have a DBMS OUTPUT statement which will get displayed when our user define error which is ex_DivZero occurs.

Now let’s group all these chunks of codes together.

Divide by zero error using PL/SQL User-define Exception in Oracle Database

SET SERVEROUTPUT ON;
DECLARE
  var_dividend NUMBER := 24;
  var_divisor NUMBER := 0;
  var_result NUMBER;
  ex_DivZero EXCEPTION;
BEGIN
  IF var_divisor = 0 THEN
    RAISE ex_DivZero;
  END IF;
  var_result := var_dividend/var_divisor;
  DBMS_OUTPUT.PUT_LINE('Result = ' ||var_result);
  EXCEPTION WHEN ex_DivZero THEN
      DBMS_OUTPUT.PUT_LINE('Error Error - Your Divisor is Zero');
END;
/

Before running this program do make sure that you have set the SERVEROUTPUT on otherwise you will not be able to see the result.

As in Step-1 we set the divisor’s value on zero that will in turn raise the user define error ex_DivZero because of this on compiling the above code you will see the string “Error Error – Your Divisor is Zero” the same one which we specified in our exception handler (step 3).

That’s the PL/SQL tutorial on how to declare user-define exception using a variable of Exception datatype. Hope you enjoyed and learnt something new. If so please make sure to share this blog on your social media and help others in learning along with me and my channel. Thanks & have a great day!

RELATED ARTICLESMORE FROM AUTHOR