O'Reilly logo

Oracle PL/SQL Language Pocket Reference by Chip Dawes, Steven Feuerstein, Bill Pribyl

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

Exception Handling

PL/SQL allows developers to raise and handle errors (exceptions) in a very flexible and powerful way. Each PL/SQL block can have its own exception section, in which exceptions can be trapped and handled (resolved or passed on to the enclosing block).

When an exception occurs (is raised) in a PL/SQL block, its execution section immediately terminates. Control is passed to the exception section.

Every exception in PL/SQL has an error number and error message; some exceptions also have names.

Declaring Exceptions

Some exceptions (see the following table) have been pre-defined by Oracle in the STANDARD package. You can also declare your own exceptions as follows:

DECLARE
   exception_name EXCEPTION;

Error

Named Exception

ORA-00001

DUP_VAL_ON_INDEX

ORA-00051

TIMEOUT_ON_RESOURCE

ORA-01001

INVALID_CURSOR

ORA-01012

NOT_LOGGED_ON

ORA-01017

LOGIN_DENIED

ORA-01403

NO_DATA_FOUND

ORA-01410

SYS_INVALID_ROWID

ORA-01422

TOO_MANY_ROWS

ORA-01476

ZERO_DIVIDE

ORA-01722

INVALID_NUMBER

ORA-06500

STORAGE_ERROR

ORA-06501

PROGRAM_ERROR

ORA-06502

VALUE_ERROR

ORA-06504

ROWTYPE_MISMATCH

ORA-06511

CURSOR_ALREADY_OPEN

ORA-06530

ACCESS_INTO_NULL

ORA-06531

COLLECTION_IS_NULL

ORA-06532

SUBSCRIPT_OUTSIDE_LIMIT

ORA-06533

SUBSCRIPT_BEYOND_COUNT

An exception can be declared only once in a block, but nested blocks can declare an exception with the same name as an outer block. If this multiple declaration occurs, scope takes precedence over name when ...

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