Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

Error handling approach

epipko
epipko Member Posts: 113 Blue Ribbon
edited Mar 8, 2018 12:02PM in SQL & PL/SQL

Hi,

I have the following setup. Procedures A, B, C do not have exception handling in them. How do I build overall exception handling so if proc B fails, the error will be propagated into outside exception handler, will get logged and emailed, but I need to continue with proc C.

The way I have it now, the execution is halted as soon as proc B fails. Does that mean I have to build exception handling in each of A,B and C procedures?

create or replace package test as

proc main;

proc A;

proc B;

proc C;

end test;

/

create or replace package body test as

procedure main is

begin

     proc A;

     proc B;

     proc C;

exception

     when others then

          ... log error

          ... email error

end main;

procedure A is

begin

     ... codel goes here

end A;

procedure B is

begin

     ... code goes here

end B;

procedure C is

begin

     ... code goes here

end C;

end test;

/

Thanks,

Tagged:

Best Answer

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Mar 7, 2018 2:34PM Accepted Answer

    You should handle the exceptions for each call, something like this:

    procedure main is

    begin

         begin

            proc A;

        exception

            when others then

                  ... log error

                  ... email error

        end;

        --

        begin

            proc B;

        exception

            when others then

                  ... log error

                  ... email error

        end;

        --

        begin

            proc C;

        exception

            when others then

                  ... log error

                  ... email error

        end;

    end main;

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Mar 7, 2018 2:34PM Accepted Answer

    You should handle the exceptions for each call, something like this:

    procedure main is

    begin

         begin

            proc A;

        exception

            when others then

                  ... log error

                  ... email error

        end;

        --

        begin

            proc B;

        exception

            when others then

                  ... log error

                  ... email error

        end;

        --

        begin

            proc C;

        exception

            when others then

                  ... log error

                  ... email error

        end;

    end main;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,371 Red Diamond
    edited Mar 7, 2018 2:36PM

    Hi,

    You can handle all the exceptions in main if you want to, uisng nested BEGIN blocks,like this:

    procedure main is 
    begin
         begin
              procA;
         exception
              ...
         end;     begin
              procB;
         exception
              ...
         end;     begin
              procC;
         exception
              ...
         end;
    end main;

    WHEN OTHERS   (unless followed by RAISE ) is usually a bad idea.  Do you really have no idea what errors those procedures might raise?

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Mar 7, 2018 2:38PM

    Please note that using WHEN OTHER without re-rising the exception is a bad practice and can lead you to many problems. You have t be 100% sure that whatever happens in ProcA, ProcB and ProcC does not have any impact to the process as a whole and that the calling process (the one that calls MAIN) does not need to know that an error was thrown.

    This is a great point to start:

  • GregV
    GregV Member Posts: 3,075 Gold Crown
    edited Mar 7, 2018 4:05PM

    In my packages I have only one WHEN OTHERS exception handler. Simply because I use packages to structure and sequence my procedures, so if one fails unexpectedly then the others must not run. In PL/SQL you deal with exceptions you expect, so if one is unexpected you don't know what to do it and you tell it to the caller.

    In your example, you say you want proc C to run even if proc B fails. But it means proc A has already run. Well, in this case, I would call proc C before proc B. What if proc A fails, should proc B and C run nevertheless?

  • epipko
    epipko Member Posts: 113 Blue Ribbon
    edited Mar 7, 2018 6:46PM

    Yes, procs A, B and C are just a collection of statements that we run nightly and they are not dependent on each other.

  • epipko
    epipko Member Posts: 113 Blue Ribbon
    edited Mar 7, 2018 6:51PM

    I've done it this way, and, to test it simply placed 1/0 into the code. Now, when proc B fails, I get "ORA-0000: normal, successful completion" in the log file

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Mar 7, 2018 8:54PM

    I'm not sure if I'm following you, but what you are saying is that in ProcB the code only has a division by 0, and when the main procedure executes it you get that specific error message?

    Because the ORA-00000 message is actually not an error:

    Error: ORA-00000: normal, successful completion

    Causa: An operation has completed normally, having met no exceptions.

    Acción: No action required.

    Can you provide detailed Oracle version (both DB and client) , which tool (SQL*Plus, SQL Developer, TOAD) are you using? I could not reproduce this on my home PC (Oracle 11g XE)

    Please also post the code, if possible, in case there is something else there.

    Regards,

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Mar 7, 2018 9:21PM

    If you are using SQLERRM, please read the documentation in detail:

    https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm

    The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODESQLERRM with no argument is useful only in an exception handler.  Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.

    So, this may be the cause of the problem:

    DECLARE

        i integer;

    BEGIN

        BEGIN

            i := 1/0;

        EXCEPTION

            WHEN OTHERS THEN

                dbms_output.put_line(SQLERRM);

        END;

        dbms_output.put_line(SQLERRM);

    END;

    Output:

    ORA-01476: divisor is equal to zero
    ORA-0000: normal, successful completion

  • epipko
    epipko Member Posts: 113 Blue Ribbon
    edited Mar 8, 2018 12:02PM

    I realized that I as not catching 1/0 exception inside of the execution block. By the time it got propagated to the outer block, it only displayed ORA-0000: normal, successful completion

    Thanks again for your help

This discussion has been closed.