#

| Chapter 8 |

# 8. Exception Handlers

Contents:
Why Exception Handling?
The Exception Section
Types of Exceptions
Determining Exception-Handling Behavior
Raising an Exception
Handling Exceptions
Client-Server Error Communication
NO_DATA_FOUND: Multipurpose Exception
Exception Handler as IF Statement
RAISE Nothing but Exceptions

In the PL/SQL language, errors of any kind are treated as exceptions -- situations that should not occur -- in your program. An exception can be one of the following:

- An error generated by the system (such as "out of memory" or "duplicate value in index")

- An error caused by a user action

- A warning issued by the application to the user

PL/SQL traps and responds to errors using an architecture of exception handlers. The exception-handler mechanism allows you to cleanly separate your error processing code from your executable statements. It also provides an event-driven model, as opposed to a linear code model, for processing errors. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section.

When an error occurs in PL/SQL, whether a system error or an application error, an exception is raised. The processing in the current PL/SQL block's execution section halts and control is transferred to the separate exception section of your program, if one exists, to handle the exception. You cannot return to that block after you finish handling the exception. Instead, control is passed to the enclosing block, if any.

Figure 8.1 illustrates how control is transferred to the exception section when an exception is raised.

#### Figure 8.1: Exception handling architecture

## 8.1 Why Exception Handling?

It is a sad fact of life that most programmers never take the time to properly bullet-proof their programs. Instead, wishful thinking often reigns. Most of us find it hard enough -- and more than enough work -- to simply write the code that implements the positive aspects of an application: maintaining customers, generating invoices, etc. It is devilishly difficult from both a psychological standpoint and a resources perspective to focus on the negative side of our life: what happens when the user presses the wrong key? If the database is unavailable, what should I do?

As a result, we write applications that often assume the best of all possible worlds, hoping that our programs are bug-free, that users will enter only the correct data in only the correct fashion, and that all systems (hardware and software) will always be a "go."

Of course, harsh reality dictates that no matter how hard you try, there will always be one more bug in your application. And your users will always find just the right sequence of keystrokes it takes to make a screen implode. The situation is clear: either you spend the time up front to properly debug and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking frantic calls from your users and putting out the fires.

You know what you should do. Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors in your programs. It is entirely feasible within the PL/SQL language to build an application which fully protects the user and the database from errors.

The exception handler model offers the following advantages:

- Event-driven handling of errors. As we've mentioned, PL/SQL exception handling follows an event-driven rather than a linear code model. No matter how a particular exception is raised, it is handled by the same exception handler in the exception section. You do not have to check repeatedly for a condition in your code, but instead can insert an exception for that condition once in the exception section and be certain that it will be handled throughout that block (and all of its enclosing blocks).

- Clean separation of error-processing code. With the exception-handling model, whenever an exception is raised, program control transfers completely out of the normal execution sequence and into the exception section. Instead of placing error-handling logic throughout different sections of your program, you can consolidate all of this logic into a single, separate section. Furthermore, if you need to add new exceptions in your program (perhaps you overlooked a possible problem, or a new kind of system error has been identified), you do not have to figure out where in your executable code to put the error-handling logic. Simply add another exception handler at the bottom of the block.

- Improved reliability of error handling. It is quite difficult for errors to go undetected with the PL/SQL error-handling model. If there is a handler, then that exception will be dealt with in the current block or in an enclosing block. Even if there is no explicit handler for that error, normal code execution will still stop. Your program cannot simply "work through" an error -- unless you explicitly organize your code to allow this.

There is no avoiding the fact that if you want to trap errors in your PL/SQL programs you will have to write some additional code. The exception handler architecture, however, minimizes the amount of code you will need to write, and offers the possibility of guarding against all problems that might arise in your application. The following sections look at how you define, raise, and handle exceptions in PL/SQL.

7.7 Tips for PL/SQL Loops |  | 8.2 The Exception Section

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.