PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to enable a procedure to continue after an error condition is one you have to make in development as you consider possible errors that could arise.
You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.
User-Defined Exceptions
PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example:
ot_failure EXCEPTION;
In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:
EXCEPTION
when OT_FAILURE then
out_status_code := g_out_status_code;
out_msg := g_out_msg;
This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:
EXCEPTION
when NO_DATA_FOUND then
g_out_status_code := 'FAIL';
RAISE ot_failure;
Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.
You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.
User-Defined Exceptions
PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example:
ot_failure EXCEPTION;
In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:
EXCEPTION
when OT_FAILURE then
out_status_code := g_out_status_code;
out_msg := g_out_msg;
This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:
EXCEPTION
when NO_DATA_FOUND then
g_out_status_code := 'FAIL';
RAISE ot_failure;
Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.
System-Defined Exceptions
PL/SQL internal exceptions.
Exception Name | Oracle Error |
| CURSOR_ALREADY_OPEN | ORA-06511 |
| DUP_VAL_ON_INDEX | ORA-00001 |
| INVALID_CURSOR | ORA-01001 |
| INVALID_NUMBER | ORA-01722 |
| LOGIN_DENIED | ORA-01017 |
| NO_DATA_FOUND | ORA-01403 |
| NOT_LOGGED_ON | ORA-01012 |
| PROGRAM_ERROR | ORA-06501 |
| STORAGE_ERROR | ORA-06500 |
| TIMEOUT_ON_RESOURCE | ORA-00051 |
| TOO_MANY_ROWS | ORA-01422 |
| TRANSACTION_BACKED_OUT | ORA-00061 |
| VALUE_ERROR | ORA-06502 |
| ZERO_DIVIDE | ORA-01476 |
In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established. This exception is illustrated in the following example:
when OTHERS then
out_status_code := 'FAIL';
out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);
This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60 characters returned from the SQLERRM function by the SUBSTR function.
