8.6.1 Combining Multiple Exceptions in a Single Handler

You can, within a single WHEN clause, combine multiple exceptions together with an OR operator, just as you would combine multiple Boolean expressions:

WHEN invalid_company_id OR negative_balance
THEN

You can also combine application and system exception names in a single handler:

WHEN balance_too_low OR ZERO_DIVIDE
THEN

You cannot, however, use the AND operator, because only one exception can be raised at a time.

8.6.2 Unhandled Exceptions

If an exception is raised in your program and that exception is not handled by an exception section in either the current or enclosing PL/SQL blocks, that exception is "unhandled." PL/SQL returns the error which raised an unhandled exception all the way back to the application environment from which PL/SQL was run. That application environment (a tool like SQL*Plus, Oracle Forms, or a Powerbuilder program) then takes an action appropriate to the situation.

A well-designed application will not allow unhandled exceptions to occur. The best way to avoid unhandled exceptions is to make sure that the outermost PL/SQL block (whether it is an anonymous block in SQL*Plus or a stored procedure in the database) contains a WHEN OTHERS clause in its exception section.

8.6.3 Using SQLCODE and SQLERRM in WHEN OTHERS Clause

You can use the WHEN OTHERS clause in the exception section to trap all otherwise unhandled exceptions, including internal errors which are not predefined by PL/SQL. Once inside the exception handler, however, you will often want to know which error occurred. You can use the SQLCODE function to obtain this information.

Consider the following situation. My application maintains companies and orders entered for those companies. My foreign key constraint on company_id in the orders table guarantees that I cannot delete a company if there are still child records (orders) in the database for that company. The following procedure deletes companies and handles any exceptions which might arise:

PROCEDURE delete_company (company_id_in IN NUMBER)
IS
BEGIN
   DELETE FROM company WHERE company_id = company_id_in;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUTLINE (' Error deleting company.');
END;

Notice the generic nature of the error message. I don't have any idea what brought me there, so I cannot pass on much useful information to the users. Did the delete fail because there are orders still present? Then perhaps I would want to delete them and then delete the company. Fortunately, Oracle provides two functions, SQLCODE and SQLERRM, which return, respectively, the error code and the error message resulting from the most recently raised exception. (These two functions are described in detail in Chapter 13, Numeric, LOB, and Miscellaneous Functions, Numeric, LOB, and Miscellaneous Functions.)

Combined with WHEN OTHERS, SQLCODE provides a way for you to handle different, specific exceptions without having to use the EXCEPTION_INIT pragma. In the next example, I trap both of the parent-child exceptions, -2292 and -2291, and then take an action appropriate to each situation:

PROCEDURE delete_company (company_id_in IN NUMBER)
IS
BEGIN
   DELETE FROM company
    WHERE company_id = company_id_in;
EXCEPTION
   WHEN OTHERS
   THEN
      /*
      || Anonymous block inside the exception handler lets me declare
      || local variables to hold the error code information.
      */
      DECLARE
         error_code NUMBER := SQLCODE;
         error_msg  VARCHAR2 (300) := SQLERRM;
      BEGIN
         IF error_code = -2292
         THEN
            /* Child records found. Delete those too! */
            DELETE FROM employee
             WHERE company_id = company_id_in;

            /* Now delete parent again. */
            DELETE FROM company
             WHERE company_id = company_id_in;

         ELSIF error_code = -2291
         THEN
            /* Parent key not found. */
            DBMS_OUTPUT.PUTLINE
               (' Invalid company ID: '||TO_CHAR (company_id_in));
         ELSE
            /* This is like a WHEN OTHERS inside a WHEN OTHERS! */
            DBMS_OUTPUT.PUTLINE
               (' Error deleting company, error: '||error_msg);
         END IF;
      END; -- End of anonymous block.

END delete_company;

8.6.4 Continuing Past Exceptions

When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. "You can never go home again," and you can never return to the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior.

Consider the following scenario: I need to write a procedure which performs a series of DML statements against a variety of tables (delete from one table, update another, insert into a final table). My first pass at writing this procedure might produce code like the following:

PROCEDURE change_data IS
BEGIN
   DELETE FROM employee WHERE ... ;
   UPDATE company SET ... ;
   INSERT INTO company_history SELECT * FROM company WHERE ... ;
END;

This procedure certainly contains all the appropriate DML statements. But one of the requirements for this program is that, in spite of the fact that these statements are executed in sequence, they are logically independent of each other. In other words, even if the delete fails, I want to go on and perform the update and insert.

With the current version of change_data, I cannot make sure that all three DML statements will at least be attempted. If an exception is raised from the DELETE, for example, then the entire program's execution will halt and control will be passed to the exception section (if there is one). The remaining SQL statements will not be executed.

How can I get the exception to be raised and handled without terminating the program as a whole? The solution is to place the DELETE within its own PL/SQL block. Consider this next version of the change_data program:

PROCEDURE change_data IS
BEGIN
   BEGIN
      DELETE FROM employee WHERE ... ;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;

   BEGIN
      UPDATE company SET ... ;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;

   BEGIN
      INSERT INTO company_history SELECT * FROM company WHERE ... ;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;
END;

With this new format, if the DELETE raises an exception, control is immediately passed to the exception section. But now what a difference! Because the DELETE statement is in its own block, it has its own exception section. The WHEN OTHERS clause in that section smoothly handles the error by doing nothing. Control is then passed out of the DELETE's block and back to the enclosing change_data procedure.

Execution in this enclosing block then continues to the next statement in the procedure. A new anonymous block is then entered for the UPDATE statement. If the UPDATE statement fails, the WHEN OTHERS in the UPDATE's own exception section traps the problem and returns control to change_data, which blithely moves on to the INSERT statement (contained in its very own block).

Figure 8.8 shows this process for two sequential DELETE statements.

Figure 8.8: Sequential DELETEs, using two different approaches to scope

To summarize: a raised exception will always be handled in the current block -- if there is a matching handler present. You can always create a "virtual block" around any statement(s) by prefacing it with a BEGIN and following it with an EXCEPTION section and an END statement. So you can control the scope of failure caused by an exception by establishing "buffers" of anonymous blocks in your code.

You can also take this strategy a step further and move the code you want to isolate into its own procedures or functions. These named PL/SQL blocks may also, of course, have their own exception sections and will offer the same protection from total failure. The advantage of using procedures and functions is that you hide all the BEGIN-EXCEPTION-END statements from the mainline program. The program is then easier to read, understand, and maintain.