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 ...
Get Oracle PL/SQL Language Pocket Reference 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.