Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: exception in procedure

    Hi,
    I have this procedure:
    CREATE OR REPLACE Procedure XXXXX
    IS
    error int;
    cursor mycur is
    select
    ..................
    begin

    for CUR in mycur
    loop

    UPDATE .............
    .................
    COMMIT;
    end loop;

    insert into MY_TABLE .............................
    EXCEPTION
    WHEN OTHERS THEN
    select 0 into error from dual;

    END;

    when the procedure go in exception it exit
    I'd like that this procedure doesn't exit.
    How can I write in exception code to avoide that exit?

    Thanks
    Raf

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Raf,

    Whenever an exception occurs in PL/SQL it will break out to the nearest surrounding exception handler. Execution continues from there (in your case, to the end of the procedure).

    This may point you in the right direction....

    PHP Code:
    PROCEDURE test ....
       
    fred number(10);
    BEGIN
       
    ... do some processing
       BEGIN
           SELECT x INTO fred
           FROM table WHERE y 
    'abc';
       
    EXCEPTION 
           WHEN NO_DATA_FOUND THEN fred 
    := NULL;
           
    WHEN OTHERS THEN RAISE;
       
    END;

       
    Process Fred
    EXCEPTION WHEN  
    ....
       
    An error occurred other than that in the select statement.
    END
    You can next exception blocks as deep as you like. This allows you to deal with exceptions which you want to can/want to deal with and propogate the others to an outer exception handler which may do logging etc.

    Hth
    Bill

Posting Permissions

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