Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    8

    Unanswered: URGENT !! Handling errors in Stored procedures

    Hi,

    I want to handle errors in the stored procedure.
    For example,
    If there is a deadlock for some resource, then i would like to handle it in a separate way.

    Please advice,

    Thanks,
    - Jay

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: URGENT !! Handling errors in Stored procedures

    Originally posted by Jayathirtha
    Hi,

    I want to handle errors in the stored procedure.
    For example,
    If there is a deadlock for some resource, then i would like to handle it in a separate way.

    Please advice,

    Thanks,
    - Jay
    I assume you want to handle if the resource is locked:

    declare
    cursor cur_upd is
    select 'x' from table
    where condition here
    for update nowait;
    v_tmp varchar2(1);
    PRAGMA EXCEPTION_INIT(record_locked,-54);
    begin
    open cur_upd;
    fetch cur_upd into v_tmp;
    ....
    close cur_upd;
    exception
    when record_locked then
    if cur_upd%isopen then
    close cur_upd;
    end if;
    .....
    end;

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Jay,

    In stored procedures you use the BEGIN ... EXCEPTION structure...

    Code:
    BEGIN
    ...
       BEGIN
          ...
       EXCEPTION WHEN <named_exception> THEN
          ...
       WHEN OTHERS THEN
          ...
       END;
    END;
    The named_exception can be a pre-defined error such as NO_DATA_FOUND, or it can be a SQL Error Code (NO_DATA_FOUND = 100). The WHEN OTHERS section is the final "catch-all" error situation. When an error is found within the BEGIN ... END portion of your code, the specific error must be found to execute that error-handling routine. WHEN OTHERS is for all other error conditions. You can also create your own exception codes and use the RAISE syntax to force your code to "error out".

    Hope this helps.
    JoeB

  4. #4
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    8
    Hi,

    Thanks for the help,

    Both the suggestions helped me ..


    Thanks again,

    Regards,
    - Jay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •