Read an Excerpt
Chapter 5: Introducing PL/SQL
In this section, you will cover the following areas related to error handling:
The three basic types of exceptions
Identifying common exceptions
Coding the exception handler
The handling of errors in PL/SQL is arguably the best contribution PL/SQL makes to commercial programming. Errors in PL/SQL need not be trapped and handled with if statements directly within the program, as they are in other procedural languages like C. Instead, PL/SQL allows the developer to raise exceptions when an error condition is identified and switch control to a special program area in the PL/SQL block, called the exception handler. The code to handle an error does not clutter the executable program logic in PL/SQL, nor is the programmer required to terminate programs with return or exit statements. The exception handler is a cleaner way to handle errors.
Three Basic Types of Exceptions
The three types of exceptions in Oracle PL/SQL are predefined exceptions, user-defined exceptions, and pragma exceptions. Exception handling in PL/SQL offers several advantages. These advantages are simplicity and flexibility. Predefined exceptions offer the developer several built-in problems that can be checked. User-defined and pragma exceptions allow for additional flexibility to build in a level of support for errors defined by the user into PL/SQL. The following discussions will illustrate the use of predefined, user-defined, and pragma exceptions.
In order to facilitate error handling in PL/SQL, Oracle has designed several"built-in" or predefined exceptions. These exceptions are used to handle common situations that may occur on the database. For example, there is a built-in exception that can be used to detect when a statement returns no data, or when a statement expecting one piece of data receives more than one piece of data. There is no invoking a predefined exception - they are tested and raised automatically by Oracle. However, in order to have something done when the predefined error occurs, there must be something in the exception handler both to identify the error and to define what happens when the error occurs. Later, in the section "Identifying Common Exceptions," several of the most common exceptions will be presented.
TIP: In order to trap a predefined exception, there must be an exception handler coded for it in the exceptions section of the PL/SQL block.
In addition to predefined exceptions, there can be created a whole host of user-defined exceptions that handle situations that may arise in the code. A user-defined exception may not produce an Oracle error; instead, user-defined exceptions may enforce business rules in situations where an Oracle error would not necessarily occur. Unlike predefined exceptions, which are implicitly raised when the associated error condition arises, a user-defined exception must have explicit code in the PL/SQL block designed to raise it. There is code required for all three sections of a PL/SQL block if the developer plans on using user-defined exceptions. The required code is detailed in the bullets that follow:
- Exception declaration In the declaration section of the PL/SQL block, the exception name must be declared. This name will be used to invoke, or raise, the exception in the execution section if the conditions of the exception occur.
- Exception testing In the execution section of the PL/SQL block, there must be code that explicitly tests for the user-defined error condition, which raises the exception if the conditions are met.
- Exception handling In the exception handler section of the PL/SQL block, there must be a specified when clause that names the exception and the code that should be executed if that exception is raised. Alternately, there should be a when others exception handler that acts as a catchall....