Re: Oracle Exceptions: Remote DB

Kid A wrote:

> 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

As mcstock suggests use WHEN OTHERS THEN as the catch-all error handler. But WHEN OTHERS THEN is a weak substitute for analyzing your code and identifying the most probable things that can go wrong.

I'd suggest that you look at the SQL statements and calculations and make a list of possible problems. A cursor that isn't open, a division by zero, a SELECT that returns no rows, an insert or update that violates a constraint. Test the conditions and record the ORA errors. Then use PRAGMA EXCEPTION_INIT to map these exceptions, where they are not already named, into exceptions you can trap and handle separately.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)