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