Re: error handling in PL/SQL

I am sorry guys. My problem is actually slightly different. I was not actually creating a stored procedure. I was actually executing the code from .sql file and it looks something like this.

declare
 cursor ....

begin

end;

So, every time i try to execute the above code, Oracle first needs to compile (resolve all the objects etc...) and if it cannot resolve an object for what ever reason, it will just throw out an error. I will definitely not have a problem if i move the code into a stored procedure and in that way I can capture the link down event too! Sorry about that! Should have thought about it before posting the question.

rgds
Daud

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0208141139.43b8bced_at_posting.google.com>...
> 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 Thu Aug 15 2002 - 01:29:07 CDT