Re: Continue despite error, (was Re: Division by zero: how to handle?)

<ctcgag_at_hotmail.com> wrote in message
news:20030606105055.303$Sf_-__at_newsreader.com...
> Norman Dunbar <Oracle_at_MSSQLBountifulSolutions.co.uk> wrote:
> > ctcgag_at_hotmail.com wrote:
> >
> > > Is there something like that for within a single query?
> > >
> > > select 1/(rownum-5) from tab; -- as a user with > 5 tables
> > >
> > > The whole query aborts, rather than just ignoring the row that would
> > > have an error.
> > >
> >
> > Morning Xho,
> >
> > how about using decode as another poster mentioned :
> >
> > select 1/decode((rownum-5), 0, Something, (rownum-5)) from tab;
>
>
> Morning Normam,
>
> I tried to hijack the thread but failed. The problem with simplifying
> a question and throwing it into some else's thread is that I get the
> simplified question answered in ways not applicable to the real question.
> :)
>
> I was just using division by zero as an error-throwing example, what I
> really want to do is:
>
> select vendor_function(col1) from table where
> vendor_function_is_not_buggy(col1);
>
> Unfortunately, the vendor wasn't kind enough to provide the
> vendor_function_is_not_buggy() function, so I was hoping I could
> tell oracle to just ignore rows that cause vendor_function() to throw
> errors, but continue the query for the rest of the rows.
>
> So I usually end up looping on a pl/sql cursor and applying the flaky
> function in the loop inside error handling blocks. That's effective,
> but having to turning every sql query into a pl/sql procedure is rather
> annoying.
>
> Thanks,
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service New Rate! $9.95/Month 50GB

Hi Xho,

Can't you put a wrapper around vendor_function with an exception handler to cater for the errors and return a value ensuring the row is not selected ?

Or am I missing the point here ?

HTH eric Received on Fri Jun 06 2003 - 14:52:37 CDT