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: 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: EXCEPTION when My_Exception then out_status_code := g_out_status_code; out_msg := g_out_msg; 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: Code: EXCEPTION when NO_DATA_FOUND then g_out_status_code := 'FAIL'; RAISE My_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. 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 | +------------------------+---------------+ 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: Code: when OTHERS then out_status_code := 'FAIL'; out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60); 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.
Pre-defined Exceptions in PL/SQL ACCESS_INTO_NULL CASE_NOT_FOUND COLLECTION_IS_NULL CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR ROWTYPE_MISMATCH SELF_IS_NULL STORAGE_ERROR SUBSCRIPT_BEYOND_COUNT SUBSCRIPT_OUTSIDE_LIMIT SYS_INVALID_ROWID TIMEOUT_ON_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE