Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/articles/oracle/)
-   -   Exception Handling (http://www.go4expert.com/articles/exception-handling-t4097/)

Sanskruti 1May2007 18:26

Exception Handling
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: SQL


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: SQL


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


| 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    |
| 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: SQL

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.

abdul_khathar 3May2007 14:15

Re: Exception Handling
Its really nice and very usefull..

thanks Sanskruti

lead.smart34 26Feb2008 17:50

Re: Exception Handling
good info

kidas 25Nov2008 22:37

Re: Exception Handling
Pre-defined Exceptions in PL/SQL

TitoChhabra 4Oct2011 14:12

Re: Exception Handling
This is good one article !!!!! thanks for sharing with us. it helps me lot.

:nonod:Thanks !!!!

All times are GMT +5.5. The time now is 07:27.