Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 7.7 Tips for PL/SQL LoopsChapter 8Next: 8.2 The Exception Section

8. Exception Handlers

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:

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

Figure 8.1

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:

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.

Previous: 7.7 Tips for PL/SQL LoopsOracle PL/SQL Programming, 2nd EditionNext: 8.2 The Exception Section
7.7 Tips for PL/SQL LoopsBook Index8.2 The Exception Section

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference