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

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