Re: Oracle Exceptions: Remote DB

"Frank" <fbortel_at_nescape.net> wrote in message news:bq5nb8$sm3$1_at_news4.tilbu1.nb.home.nl...
| 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
|

all unhandled exceptions propagate. the suggestion is to use RAISE_APPLICATION_ERROR in the common error handler to pass the transformed error message, albeit with a user-defined error code

CREATE OR REPLACE procedure err_handler ( ip_err in number, ip_msg in varchar2 )
is
begin
 if ip_err = -2292
 then -- logic to transform ip_msg

      raise_application_error(-20001, 'Transformed message for error 2292');  else
   raise_application_error(-20000, ip_msg );

• or
• raise ip_err; end if; end err_handler; /

CREATE OR REPLACE procedure select_bad_dept is
  r1 dept%rowtype;
begin
  select *
  into r1
  from dept
  where deptno = -1;
exception
when others
then err_handler(sqlcode,sqlerrm);
end select_bad_dept;
/

CREATE OR REPLACE procedure delete_all_depts is
begin

   delete from dept;
exception
when others
then err_handler(sqlcode, sqlerrm);
end delete_all_depts;
/

SQL> exec select_bad_dept
BEGIN select_bad_dept; END;

*
ERROR at line 1:

ORA-20000: ORA-01403: no data found
ORA-06512: at "SCOTT.ERR_HANDLER", line 8
ORA-06512: at "SCOTT.SELECT_BAD_DEPT", line 11
ORA-06512: at line 1

SQL> exec delete_all_depts;
BEGIN delete_all_depts; END;

*
ERROR at line 1:

ORA-20001: Transformed message for error 2292
ORA-06512: at "SCOTT.ERR_HANDLER", line 6
ORA-06512: at "SCOTT.DELETE_ALL_DEPTS", line 7
ORA-06512: at line 1

• Mark Stock