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 or other built-in packages, such as UTL_FILE. 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-00061

TRANSACTION_BACKED_OUT

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-01725

USERENV_COMMMITSCN_ERROR

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

ORA-09592

CASE_NOT_FOUND

ORA-30625

SELF_IS_NULL

ORA-29280

INVALID_PATH

ORA-29281 ...

Get Oracle PL/SQL Language Pocket Reference, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.