[Chapter 8] 8.5 Raising an Exception

8.5 Raising an Exception

There are four ways that you or the PL/SQL runtime engine can raise an exception:

The PL/SQL runtime engine raised a named system exception. These exceptions are raised automatically by the program. You cannot control when PL/SQL will raise a system exception. You can, however, write code to handle those exceptions when they are raised.
The programmer raises a named exception. The programmer can use an explicit call to the RAISE statement to raise a programmer-defined or system-named exception.
The programmer raises an unnamed, programmer-defined exception. These are raised with an explicit call to the RAISE_APPLICATION_ERROR procedure in the DBMS_STANDARD package.
The programmer re-raises the "current" exception. From within an exception handler, you can re-raise the same exception for propagation to the enclosing block.

8.5.1 Who Raises the Exception?

The following sections show how you can let PL/SQL raise a system error or you can check for the error yourself and then raise that same system error.

8.5.1.1 PL/SQL raises ZERO_DIVIDE exception

In the following example, I raise my own exception, sales_domination, when the percentage of a customer's sales is over 50% of total sales. If, on the other hand, the total_sales is zero (as will be the case in the senseless code below), PL/SQL will automatically raise the ZERO_DIVIDE exception. Because I include a handler for that specific problem, the application does not abort when this code is executed. Instead, a message is displayed informing the user of a serious problem:

DECLARE
   total_sales NUMBER := 0;
   cust_sales NUMBER;
   sales_domination EXCEPTION;
BEGIN
   SELECT SUM (sales) INTO cust_sales
     FROM invoice WHERE customer_id = 1001;
   IF cust_sales / total_sales > .5
   THEN
      RAISE sales_domination;
   END IF;
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE
         (' We haven''t sold anything. We are bankrupt!');

   WHEN sales_domination
   THEN
      DBMS_OUTPUT.PUT_LINE
         (' Customer 1001 accounts for more than half of all sales!');
END;

Notice that there is no RAISE statement for the ZERO_DIVIDE exception in the body of the program. Instead, I leave it to PL/SQL to raise such internally generated exceptions. There is no restriction, however, on a programmer's raising a predefined exception.

8.5.1.2 Programmer raises ZERO_DIVIDE exception

I could recode the previous anonymous block as follows:

DECLARE
   total_sales NUMBER := 0;
   cust_sales NUMBER;
   sales_domination EXCEPTION;
BEGIN
   SELECT SUM (sales) INTO cust_sales
     FROM invoice WHERE customer_id = 1001;

   /* Check for zero divisor and raise exception if necessary */
   IF total_sales = 0
   THEN
      RAISE ZERO_DIVIDE;
   ELSIF cust_sales / total_sales > .5
   THEN
      RAISE sales_domination;
   END IF;
EXCEPTION
   ... unchanged ...
END;

Here, my own code raises the ZERO_DIVIDE exception because as author of the program I know that a total_sales of zero will result in a division by zero. With either approach, the result is the same. Regardless of how the ZERO_DIVIDE exception is raised, the same exception handler will trap the error.

8.5.1.3 Impact of unhandled exceptions

If an exception is raised in a PL/SQL block and goes unhandled, there are several consequences to consider. First, if the program contains OUT or IN OUT parameters, then the PL/SQL runtime engine does not assign values to those parameters. Any changes to those parameters made during the program execution are, in essence, rolled back. Second, the runtime engine does not roll back any database work performed by that PL/SQL block. Instead, you must issue an explicit ROLLBACK statement to achieve this effect.

8.5.2 Re-Raising an Exception

When you are inside an exception handler in an exception section, you can re-raise the exception that "got you there" by issuing an unqualified RAISE statement as follows:

RAISE;

Since you do not specify an exception, the PL/SQL runtime engine re-raises the current exception (whose error number would be returned by a call to the SQLCODE function).

Here is an example of using raise in this way:

EXCEPTION
   WHEN OTHERS
   THEN
      send_error_to_pipe (SQLCODE);
      RAISE;
END;

This re-raise functionality comes in very handy when you need to raise exceptions which have not been assigned a name through use of the EXCEPTION_INIT pragma.

8.5.3 Exceptions Raised in a Declaration

When an exception is raised in the declaration section of a block, control is passed immediately to the enclosing block, if there is one, not to the exception section of the current block, as shown in Figure 8.6.

The assignment of the default value to little_string is too big for the variable. This causes PL/SQL to raise the VALUE_ERROR exception. This exception will not, however, be handled by the VALUE_ERROR handler in the anonymous block's exception section. Instead, PL/SQL will terminate the anonymous block and try to handle the exception in early_failure's exception section.

Figure 8.6: VALUE_ERROR raised in nested block declaration section

The reason for this behavior is simple, as we describe in the next section.

8.5.4 Exceptions Raised in an Exception Handler

You can raise another exception from within an exception handler, but that exception can be handled only in the exception section of an enclosing block -- never in the current block. The exception section of the current PL/SQL block will only handle exceptions raised in the execution section of that block.

When an exception is raised, the execution in the current block is immediately terminated, and control is passed in to the exception section, as shown in Figure 8.7. Once a match for the exception has been found, the rest of the exception section is inaccessible. At this point it is impossible to re-enter this same exception section because the corresponding execution section has been exited.

Figure 8.7: Exception raised in exception handler immediately exits the exception section

Why would you want to raise an exception inside an exception handler? You will find it useful to raise new exceptions from the current exception section when you do not want the enclosing block to continue normal processing after an exception in the subblock. You may instead want execution to branch off in a different direction, or to skip the rest of the enclosing block and move immediately to the enclosing block of that enclosing block.

In the following example, I have a triple nesting of anonymous blocks. When the innermost block raises an exception, I want to terminate both the second and third blocks, but continue normal processing in the outermost block. Raising an exception from within an exception block produces this behavior for me:

DECLARE -- Outermost block
BEGIN

   DECLARE -- First sub-block.
      -- An exception whose scope is the two sub-blocks.
      skip_sub_block EXCEPTION;
   BEGIN
      ... executable statements for first sub-block ...

      BEGIN -- Second sub-block.
         ... executable statements for innermost block ...
      EXCEPTION
         WHEN NO_DATA_FOUND -- A table fetch returned no values.
         THEN
            RAISE skip_sub_block; -- Raise exception in enclosing block.
      END;

   EXCEPTION
      WHEN skip_sub_block
      THEN
         NULL; -- Terminate this sub-block, return to main block.
   END;

   -- Now continue with main block processing.
   ... executable statements ...

END;


8.4 Determining Exception-Handling Behavior		8.6 Handling Exceptions