PL/SQL 101: Raising exceptions in PL/SQL

Most of the time that an exception is raised in your application, Oracle Database will do the raising. That is, some kind of problem has occurred during the execution of your code. You have no control over that; once the exception has been raised, all you can do is handle the exception - or let it "escape" unhandled to the host environment.

You can, however, raise exceptions yourself in your own code. Why would you want to do this? Because not every error in an application is the result of a failure of internal processing in the Oracle database. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong.

PL/SQL offers two ways for you to raise an exception:

1. The RAISE statement
2. The RAISE_APPLICATION_ERROR built-in procedure

Of course, you could also force the raising of an exception by Oracle, with code like this:

BEGIN
   my_variable := 1/0;
END;

But I am going to show you how to terminate block execution with lots more control and information than that will do for you.

RAISE

Use RAISE to raise a previously-defined exception. It could be a system exception, provided by PL/SQL itself, such as NO_DATA_FOUND. Or it could be an exception that you declare yourself.

Suppose I have a procedure that accepts a new salary to be assigned to an employee. The rule you want to enforce is that the new salary cannot be negative. This is a sort of "value error", so you could simply raise that pre-defined exception.

[Note: all code in this post may found in my LiveSQL script.]

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF salary_in < 0 
   THEN
      RAISE VALUE_ERROR;
   END IF;

   ... rest of procedure
END;

When you run this procedure as follows:

BEGIN
   use_salary (salary_in => -1);
END;

You will see this error:

ORA-06502: PL/SQL: numeric or value error 

So you stopped the program from continuing, and that's great. It can, however, be confusing to people maintaining your code and users seeing your error message to fall back on the generic PL/SQL exception. So you could declare your own exception in exactly the same way that PL/SQL defines exceptions like VALUE_ERROR.

Which probably has you thinking: how does PL/SQL define these exceptions? They are, for the most part, found in a special package named STANDARD. You probably haven't heard of it, and that's OK. It is one of the two default packages of PL/SQL: STANDARD and DBMS_STANDARD.

By default, I mean that you can reference elements in these packages without including their package name. For example, I could reference VALUE_ERROR in the use_salary procedure as follows:

PROCEDURE use_salary (salary_in IN NUMBER) 
   AUTHID DEFINER
IS
BEGIN
   IF salary_in < 0 
   THEN
      RAISE STANDARD.VALUE_ERROR;
   END IF;

But I don't have to. If the compiler cannot otherwise resolve the reference to VALUE_ERROR (without the package name), it will then see if that identifier can be resolved in STANDARD or DBMS_STANDARD.

And if we look inside the STANDARD package, we find code like this:

  VALUE_ERROR exception;
  pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');

Let's apply that same technique in use_salary:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
   negative_salary EXCEPTION;
   PRAGMA EXCEPTION_INIT (negative_salary, '-6502');
BEGIN
   IF salary_in < 0 
   THEN
      RAISE negative_salary;
   END IF;

   ... rest of procedure
END;

And when I execute the procedure and pass it -1, I see the same error information:

ORA-06502: PL/SQL: numeric or value error

So what's the advantage of switching to own exception? In this case (so far), very minor: your code raises an exception which by name tells you and anyone maintaining the code later what the problem is.The information presented to the user, however, is no more informative than before. If you want to change that message to something more understandable to users, you will want to "switch" to RAISE_APPLICATION_ERROR, which I cover below.

Before we move on, though, here are some thoughts on why you might want to define your own exceptions: Suppose I have several error conditions, and for each error condition, I need to take different actions. I could write code like this in my executable section:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
BEGIN
   CASE
      WHEN salary_in < 0 
      THEN 
         notify_support (
            'Negative salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in > 10000 
      THEN 
         notify_support (
            'Too large salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in < 100 
      THEN 
         notify_hr (
            'No one should be treated so shabbily! ' || 
            salary_in);  
         RAISE VALUE_ERROR;
      ELSE
         /* No problems, proceed with normal execution*/
         NULL;
   END CASE;

   /* Rest of procedure */
END;

But then I have filled up the first part of executable section with error handling code. That makes it harder to see through the "noise" and focus on the actual algorithm I wrote to implement use_salary. A better approach is as follows:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */

EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);  
      RAISE VALUE_ERROR;
END;

Now all my exception handling code is in the exception section where it belongs.

Notice that I do not use the pragma to assign an error code to my exceptions. That means that the value returned by SQLCODE will be 1. All the time. Which is not too helpful in terms of distinguishing between the errors. In this case, it does not matter, because we are logging the specifics with support or HR and then causing the procedure to fail with a generic message via VALUE_ERROR (often a good idea from the standpoint of security).

RAISE_APPLICATION_ERROR

This built-in, actually defined in the DBMS_STANDARD package, should be used when you need to communicate an application-specific error message to your users.

With RAISE_APPLICATION_ERROR, you provide both the error code and error message as follows:

BEGIN
   RAISE_APPLICATION_ERROR (-20000, 'Say whatever you want');
END;

And when you run the above block it will fail with an unhandled exception, displaying this information:

ORA-20000: Say whatever you want 

So if I do want to communicate a context-sensitive and user-informative message when there is a failure in my use_salary procedure, I would  do something like this:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */

EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20001,
         'Negative salaries are not allowed. Please re-enter.');

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20002,
         'We are not nearly that generous. Please re-enter.');

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);
      RAISE_APPLICATION_ERROR (-20003,
         'C''mon, a person''s gotta eat! Please re-enter.');
END;

And then when I execute the procedure and pass in -1, I will see:

ORA-20001: Negative salaries are not allowed. Please re-enter.

Some things to remember about RAISE_APPLICATION_ERROR:

• You must provide an error code between -20999 and -20000.
• If you do not pass TRUE for the third parameter, then the full error stack will not be kept, and only the most recent error message will be shown.
• You can pass a string of up to 32767 characters to the built-in, but that string will be truncated to either 512 or 1899 bytes when calling SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK respectively.

To Conclude

Usually we leave it to the PL/SQL runtime engine to raise exceptions when an error occurs. Sometimes, however, you will need to raise your own exceptions. To do this, you can use RAISE or RAISE_APPLICATION_ERROR. The latter is especially helpful when you need to communicate an application-specific error message to your users.

Take advantage of PL/SQL's separate exception handling section to centralize as much of your error-related logic as possible. Keep your executable section clean and focused on executing the "positive" statements - what should be happening when there are not any errors.

Resources

You can run all of the above code via my LiveSQL script.

PL/SQL Documentation: Raising Exceptions Explicitly

YouTube Playlist: Practically Perfect PL/SQL Nine Good to Knows for Error Management