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.
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:
My_Exception EXCEPTION;
In this case, the exception name is My_Exception. Code associated with this handler is written in the EXCEPTION specification area as follows:
This exception is defined 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:
Within this exception is the RAISE statement that transfers control back to the My_Exception exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.
Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception.
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:
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.
SQLERRM only returns a valid message when called inside an exception handler unless an argument is passed to the function that is a valid SQL error number. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text associated with that Oracle error code. In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes.
FAIL: init_line_items ORA-01001: invalid cursor
This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in the following example:
FAIL: calc_ship_charges
In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text.
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:
My_Exception EXCEPTION;
In this case, the exception name is My_Exception. Code associated with this handler is written in the EXCEPTION specification area as follows:
Code: SQL
EXCEPTION
when My_Exception then
out_status_code := g_out_status_code;
out_msg := g_out_msg;
Code: SQL
EXCEPTION
when NO_DATA_FOUND then
g_out_status_code := 'FAIL';
RAISE My_Exception;
System-Defined Exceptions
Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception.
PL/SQL internal exceptions
Code:
+------------------------+---------------+ | 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 | +------------------------+---------------+
Code: SQL
when OTHERS then
out_status_code := 'FAIL';
out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);
SQLERRM only returns a valid message when called inside an exception handler unless an argument is passed to the function that is a valid SQL error number. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text associated with that Oracle error code. In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes.
FAIL: init_line_items ORA-01001: invalid cursor
This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in the following example:
FAIL: calc_ship_charges
In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text.
Thanks !!!!