PL/SQL Exceptions  «Prev  Next»

Lesson 2 What is an exception and why trap it?
Objective Define an exception and its purpose.

What is PL/SQL Exception

Runtime errors can arise from design faults, coding mistakes, hardware failures, and several other sources. Certain kinds of errors can be handled via a PL/SQL block. In PL/SQL, an error condition is called an exception. Exceptions can be either internally defined or user defined. An exception is declared within the declarative part of a PL/SQL block by naming the exception and defining it as a datatype of EXCEPTION. Unlike internal exceptions, user-defined exceptions must be given names.
When an error occurs, an exception is raised whereby normal execution stops and the control is transferred to the exception handling routine of the PL/SQL block. Internal exceptions are raised automatically by the runtime system, whereas user-defined exceptions are raised explicitly by the RAISE statement.
Use the following Slide Show to learn more about the use of exceptions within PL/SQL.

An exception is an identifier in PL/SQL raised during the execution of a block that terminates its main body of actions.
1) An exception is an identifier in PL/SQL raised during the execution of a block that terminates its main body of actions.

An exception is raised when an Oracle error occurs. Alternatively, you can also raise it explicitly within your code.
2) An exception is raised when an Oracle error occurs. Alternatively, you can also raise it explicitly within your code.

You specify an exception handler routine within your PL/SQL block to trap it. If you do not handle the exception, it is propagated to the calling environment.
3) You specify an exception handler routine within your PL/SQL block to trap it. If you do not handle the exception, it is propagated to the calling environment.


Handling raised Exceptions

To handle raised exceptions, separate routines called exception handlers are written. When an exception handler is invoked, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. You already learned about the host environment in an earlier module.

PL/SQL Exceptions
PL/SQL Exceptions

OTHERS hander

The optional OTHERS exception handler acts as the handler for all exceptions that the block does not name specifically. Thus, a block can have only one OTHERS hander.
Use of the OTHERS handler guarantees that no exception will go unhandled. If two or more exceptions should be executed in the same sequence of statements, list the exception names within the WHEN clause, separating them by the keyword OR, as shown in the following example: 

EXCEPTION
WHEN over_limit OR under_limit OR VALUE_ERROR THEN

-- handle the error

WHEN OTHERS THEN

-- handle all other errors
END;

If any of the exceptions in the above list is raised, PL/SQL executes the associated sequence of statements. The keyword THERS is not allowed in such a list of exception names; it must appear by itself.

Guidelines for handling Exceptions

Here are some of the guidelines you want to keep in mind when developing exception handling routines:
  1. There can be any number of exception handlers.
  2. Each handler can associate a sequence of statements with a list of exceptions.
  3. An exception name can appear only once within the exception handling part of a block.
  4. Only one exception can be active at a time within the exception handling part of a block.
  5. If an exception is raised inside a handler, the block that encloses the current block is the first block searched to find a handler for the newly raised exception. From there on, the exception propagates normally.
In the next lesson, you will learn about the different types of exceptions.