Re: error handling in PL/SQL

daud11_at_hotmail.com (Daud) wrote in message news:<f0bf3cc3.0208140633.2b055ed7_at_posting.google.com>...
> I have a procedure that looks something like below.
>
> create or replace procedure AAA ....
> cursor mycursor is select ... from mytable_at_remotedb;
> ....
> begin
>
> open mycursor;
> ...
>
>
> end;
>
> The problem I am having is that sometimes the remote db is not up and
> I would like to be able to handle the error that would be raised
> gracefully. How do I do that? I have tried to put EXCEPTION in the
> 'begin....end' block but that did not worked. Any way to solve this?
>
> rgds
> Daud

I would think that coding an exception handler would be the way to do this providing the handler trapped the specific error message returned by Oracle. How did you go about coding the exception block? And did you use a pragma statement to tie the expected Oracle error to a program error? Example:
pragma exception_init(constraint_viol,-02291

I haven't actually tried to capture the error returned on a remote query when the remote db is unavailable, but the above has worked for every other Oracle error I have tried so I would think it would work here.

HTH -- Mark D Powell -- Received on Wed Aug 14 2002 - 14:39:12 CDT