Exception Handling

Discussion in 'Oracle' started by Sanskruti, May 1, 2007.

  1. Sanskruti

    Sanskruti New Member

    Joined:
    Jan 7, 2007
    Messages:
    108
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Consultant
    Location:
    Mumbai, India
    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.
     
    shabbir likes this.
  2. abdul_khathar

    abdul_khathar New Member

    Joined:
    Nov 28, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    software Engineer
    Location:
    coimbatore, INDIA
    Its really nice and very usefull..

    thanks Sanskruti
     
  3. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
  4. kidas

    kidas Super Moderator

    Joined:
    Nov 25, 2008
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.club-oracle.com
    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
     
  5. TitoChhabra

    TitoChhabra New Member

    Joined:
    Oct 4, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hi,
    This is good one article !!!!! thanks for sharing with us. it helps me lot.


    :nonod:Thanks !!!!
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice