Error handling approach

epipko

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,

L. Fernigrini

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;

L. Fernigrini

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

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

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

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

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

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

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

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 SQLCODE.  SQLERRM 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

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