PL/SQL Exceptions  «Prev  Next»

Lesson 1

Handling PL/SQL Exceptions

Error handling is probably one of the most important parts of programming. In a perfect world, if all errors could be trapped, there would be no bugs in your code.
PL/SQL provides for extensive error handling. There are two ways to trap errors: implicitly and explicitly. In this module, you will learn all about them.

Module objectives

By the end of this module, you will know how to:
  1. Define an exception and its purpose
  2. Categorize the different types of exceptions
  3. Code predefined server exceptions
  4. Use an implicit user-defined exception
  5. Raise an exception based on user-defined criteria
  6. Describe the use of functions and procedures within error handling

Exception Types and Scope

You have two types of errors: 1) compilation errors and 2) run-time errors. Compilation errors occur when you make an error typing the program or defining the program. The typing errors include forgetting a reserved word, keyword, or semicolon. These lexical errors are caught when the plain text file is parsed during compilation. Parsing is the process of reading a text file to ensure that it meets the lexical usage rules of a programming language. Run-time errors occur when actual data fails to meet the rules defined by your program unit.
Variable scope moves from the outermost block to the innermost. While variable scope begins at the outside and narrows as we nest program units, exception handling works in the opposite direction. Exceptions in the innermost blocks are handled locally or thrown to the containing block in sequence until they arrive at the originating session. Figure 7-1 shows this exception management process. Compilation errors are often seen quickly because they fail during the parsing phase. Some deferred errors go unhandled until you run the programs with data values that trigger the error.

Figure 7-1: Exception scope and routing

Deferred Compilation Errors

You create deferred compilation errors when actual data values do not fit during assignment because they are too large or the wrong datatype. All compilation errors are thrown back to the session and cannot be handled by your local exception handler, but you can catch them in a wrapper (containing outer) block. Run-time execution errors can always be caught and processed by the local or external exception block. Run-time errors in exception blocks can only be caught by an outer block exception handler. You can also opt not to catch errors and have them thrown back to the originating SQL*Plus session.
In the next lesson, we will begin by defining an exception.

Ad Oracle PL/SQL Programming