Re: Oracle Exceptions: Remote DB

mcstock wrote:

> "Frank" <fbortel_at_nescape.net> wrote in message
> news:bq33fv$j4p$1_at_news4.tilbu1.nb.home.nl...
> | mcstock wrote:
> |
> | > look into the WHEN OTHERS exception handler clause
> | >
> | > you can call a common procedure, passing the SQLCODE value to your
> routine
> | >
> | > also, sounds like you may be aware of this already, any unhandled
> exception
> | > gets propagated to the calling procedure -- so if you pass SQLCODE to
> your
> | > common procedure, it can then raise an appropriate exception (typically
> with
> | > RAISE_APPLICATION_ERROR) which would replace the one caught within the
> | > calling procedure, and pass it up the stack
> | >
> | > --mcs
> | >
> | >
> | > "Kid A" <paulkist_at_hotmail.com> wrote in message
> | > news:5327b5de.0311250730.4de56c2f_at_posting.google.com...
> | > | Hello everyone,
> | > |
> | > | I am setting up an exception handling framework for my stored
> | > | procedures. It seems as if there are thousands of possibilities of
> | > | errors that can occurr, how do you know which ones to go with.
> | > |
> | > | For example: I am making calls via DB link, and I am sure there are
> | > | 100 or so errors related to connection failures, authentication, etc.
> | > | Do I have to catch each error related to a remote call in order to
> | > | handle them, or is there way to group a bunch of exceptions together
> | > | in one handler, in order to handle all the remote connection problems
> | > | in ONE place in the code.
> | > |
> | > | Does this question make sense? I'd be interested to learn from your
> | > | experience with handling exceptions in PL/SQL.
> | > |
> | > | Thanks
> | > |
> | > | -PK
> | >
> | >
> | That would be a RAISE; a RAISE_APPLICATION_ERROR will do that,
> | at the specified point - a RAISE will re-raise the error to tha
> | calling unit
> |
> | --
> | Regards, Frank van Bortel
> |
>
> i did mean RAISE_APPLICATION_ERROR -- one important feature of a common
> error handler is to translate the generic oracle error into something more
> useful, ie.
>
> ORA-02292: integrity constraint (SCOTT.EMP_DEPTNO_FK) violated - child
> record found
>
> to something like
>
> ORA-20001 Cannot delete a department that currently has employees
>
> or to otherwise map oracle errors to application logic errors
>
> if it is desirable to simply to some error-time processing and then
> propagate the original error (without modification or mapping), then RAISE
> would be the technique
>
> -- mark stock
>
>

Sorry - the point is that RAISE_APPLICATION_ERROR will not pass the error up the stack not return to the caller. That's why - after entering the exception handler (and possibly transforming the error message the way you suggest), you must re-raise the error.
I mean it has been handled, and that's the end of it, unless you re-raise, and thus allow passing the error stack to the caller.

-- 
Regards, Frank van Bortel