Skip to main content

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

Comments

  1. Hello Steven,

    Just a small remark:

    I think that what you meant in your text was
    DBMS_UTILITY.FORMAT_ERROR_STACK and not
    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

    Cheers & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks for your usual close reading, Iudith. I have made the change you recommended.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel