O'Reilly logo

Oracle SQL: the Essential Reference by David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

7.5. Exception Section

Even the best-written PL/SQL programs encounter errors or unexpected events. PL/SQL provides a powerful and flexible method for handling both expected and unexpected events through the use of exceptions and exception handlers. Any Oracle error (those reported with Oracle error numbers in the form ORA-xxxxx) PL/SQL runtime error, or user-defined condition (not necessarily an error), can be handled.

PL/SQL compile errors are not handled by PL/SQL exception handling, since these errors occur prior to execution of the PL/SQL program.

PL/SQL error handling is fairly straightforward. When an error occurs in a running PL/SQL program, an exception is raised and the appropriate exception handler takes control.

7.5.1. Types of Exceptions

PL/SQL handles two types of exceptions: predefined and user-defined. User-defined exceptions must be declared in the DECLARE section (see Section 7.3.8 earlier in this chapter) of a PL/SQL block. Predefined exceptions are supplied to handle the most common types of errors and are summarized in Table 7-6.

Table 7-6. Predefined PL/SQL Exceptions
Exception Oracle Error Description
CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.
DUP_VAL_ON_INDEX ORA-00001 A unique constraint was violated.
INVALID_CURSOR ORA-01001 An illegal operation, such as an attempt to close an already closed cursor, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required