As discussed in the introduction to PL/SQL exception handling there are three ways of declaring user-define exceptions in Oracle PL/SQL. Among those three we have already discussed and learnt the first way in the previous tutorial. Today in this blog we will take a step ahead and see the second way of declaring user define exception and learn how to declare user-define exception using RAISE_APPLICATION_ERROR method.

What is RAISE_APPLICATION_ERROR method?

RAISE APPLICATION ERROR is a stored procedure which comes in-built with Oracle software. Using this procedure you can associate an error number with the custom error message. Combining both the error number as well as the custom error message you can compose an error string which looks similar to those default error strings which are displayed by Oracle engine when an error occurs.

How many errors can we generate using RAISE_APPLICATION_ERROR procedure?

RAISE_APPLICATION_ERROR procedure allows us to number our errors from -20,000 to -20,999 thus we can say that using RAISE_APPLICATION_ERROR procedure we can generate 1000 errors.

Raise_application_error is part of which package?

You can find RAISE_APPLICATION_ERROR procedure inside DBMS_STANDARD package.

Syntax of Raise_Application_Error

raise_application_error (error_number, message [, {TRUE | FALSE}]);

Here the error_number is a negative integer in the range of -20000.. -20999 and the message is a character string up to 2048 bytes long. In case of the optional third parameter being TRUE, the error is placed on the pile of all the previous errors. However in case of FALSE (the default) parameter, the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and you do not need to qualify references to package STANDARD. 

Example of RAISE_APPLICATION_ERROR procedure

In the following example we will take an input of numeric datatype from the user and check if it is 18 or above. If it is not then the user-define error, which we will declare, will be raised otherwise there will be the normal flow of execution of the program.

Step1: Set the server output on

If we want to see the result returned by the PL/SQL program on the default output screen then we will have to set the server output ‘on’ which is by default set to ‘off’ for the session.

SET SERVEROUTPUT ON;

Step 2: Take User Input

Though we can hardwire the values in our code as we did in the last tutorial but it is not that fun. In order to make the code more dynamic I decided to accept the input by user this time by letting them to enter the value into a pop-up box with a customized message printed on it.

We can take the input using pop-up box with a customized message printed on it using ACCEPT command in Oracle PL/SQL.

ACCEPT var_age NUMBER PROMPT 'What is your age?';

Command starts with the keyword accept followed by the name of the variable and its datatype. In our case the name is var_age and datatype is NUMBER. That is the first half of the statement. This part will help us in storing the input value. The other half of the statement will be responsible for printing the customized message on the pop-up box. Using the keyword PROMPT which is right after the datatype of the variable, you can specify any desired string which you want printed on your pop-up box. In our case this customized message will be ‘What is your age?’

If you want me to do an extensive tutorial explaining the ACCEPT command in detail then share this blog using the hashtag #RebellionRider. You can also write to me on my twitter @RebellionRider.

Step 3: Declare Variable

DECLARE
  age   NUMBER := &var_age;

Due to the scope restriction we cannot use the value stored into the variable which we used in the accept command. This means we cannot directly use that value into our PL/SQL program. We can solve this problem by assigning the value that was stored into the variable var_age to a variable which is local to the PL/SQL block. That is exactly what we did in the above segment of the code.

In the above code segment we declared a local variable with name ‘age’ and assigned the value stored into the variable var_age using the assignment operator.

Step 4: Declare the user-define exception by using RAISE_APPLICATION_ERROR procedure

BEGIN
  IF age < 18 THEN
   RAISE_APPLICATION_ERROR (-20008, 'you should be 18 or above for the DRINK!');
  END IF;

Here in this code segment we declared the user-define exception using RAISE_APPLICATION_ERROR procedure. This procedure is called using two parameters. In which first parameter is the negative number which, in my case, is -20008 and the second number is a string which gets displayed if the same error occurs.

Now the question is when will this error occur?

As you can see that the code is enclosed inside an IF-THEN conditional control block which is making sure that the error will be raised only if the age of the user is less than 18 years.

Step 5: Executable statement

DBMS_OUTPUT.PUT_LINE('Sure, What would you like to have?');

Executable statements are those that get compiled and run when there is no error and the program has a normal flow of execution. In order to make the code simple and easy to understand I just wrote a single statement which is the DBMS OUTPUT statement.

Step 6: Write the Exception handler

Now that we have declared as well as raised the user-define exception next we need to write the exception handler for it. As said in the previous PL/SQL tutorial that in the Exception Handler section we specify what will happen when the error which you raised will trigger.

EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

In this exception handling section I have called a SQLERRM function using DBMS OUTPUT statement. This is a utility function provided by Oracle which retrieves the error message for the last occurred exception.

Let’s compile all these small chunks of code into one big program.

User-Define Exception Using Raise_Application_Error Procedure

SET SERVEROUTPUT ON;
ACCEPT var_age NUMBER PROMPT 'What is yor age';
DECLARE
  age   NUMBER := &var_age;
BEGIN
  IF age < 18 THEN
    RAISE_APPLICATION_ERROR (-20008, 'you should be 18 or above for the DRINK!');
  END IF; 
  DBMS_OUTPUT.PUT_LINE ('Sure, What would you like to have?'); 
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/ 

This is a short yet descriptive tutorial on how to declare user-define exception using Raise_Application_Error procedure in Oracle Database. Hope you learnt something new and enjoyed reading. You can help others in learning as well as help me and my channel in growing by sharing this blog on your Social Media. Thanks & have a great day!