Re: WHENEVER SQLERROR does'nt always work

Hi!

Just a note, the whole Oracle Applications database patching and upgrading system uses WHENEVER OSERROR and SQLERROR constructs (tens thousands of scripts), so it definitely is possible to get this mechanism to work...

Tanel,

"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.196516ac52c81e3a989771_at_news.cis.dfn.de...
> In article <d2eb532b.0306260409.f2683c1_at_posting.google.com>,
> ni4ni_at_hotmail.com says...
> > Hi
> >
> > I have a set of sql scripts that creates several tables and them
> > populates them with rows (via insert statements).
> > Each script begins with WHENEVER SQLERROR... and WHENEVER OSERROR...
> > So it should halt once an error occures. I thought this will cover all
> > possible errors.
> > The problem is that it doesnt work with all kinds of errors. For
> > example, if I wrote "nsert into" instead of "insert into" then
> > SQL*PLUS throws this error message: SP2-0734: unknown command
> > beginning "NSERT INTO..." but execution doesnt stop.
> >
> > Does it mean that WHENEVER SQLERROR doesnt catch "SP2" errors ? is
> > there a way to catch this kind of errors ? I could'nt find a clue in
> > SQL*PLUS documentation.
> >
> >
> > T.I.A
> >
> > Guy
> >
>
> Don't know if there is a way to catch these but the reason that the
> 'whenever sqlerror' doesn't work is that the errors are that the program
> sqlplus doesn't understand the command - it doesn't get as far as oracle
> per se.
>
> You probably shouldn't be too concerned after all once you have written
> and tested your scripts, the only thing that will fail *will* give rise
> to a SQLERROR or OSERROR....
>
> HTH
>
> --
>
> jeremy
Received on Thu Jun 26 2003 - 10:46:30 CDT