Re: Oracle exceptions/error handling

dfairman16_at_hotmail.com (David) wrote in message news:<b4cefdce.0205200927.2c45b419_at_posting.google.com>...
> Hi there.
>
> I've have a requirement to write several Oracle procedures and have
> come across an issue with exceptions that I can't resolve on my own.
>
> The crux of my problem is the following:
>
> SELECT something INTO avariable FROM somewhere
>
> An ORA-6502 (value error) is raised, which I catch, because the query
> doesn't returns an empty resultset on occasions (and thus the contents
> of 'avariable' are undefined).
>
> Rather than catching the error, I want the procedure to continue with
> the SQL underneath where the exception was raised, sort of like an ON
> ERROR JUSTIGNOREANDCARRYONWITHTHEREMAININGSQLBELOW.
>
> I'm embarassed to tell you my current solution but essentially it
> involves catching the exception, then using a GOTO to resume in the
> main body. And I know it is far from elegant.
>
> There must be a better way. Ideas anyone?
> Thank you
> Dave

Well you could do a couple of things.
1) do a select nvl(something,something_else) from somewhere 2) make it into its own block when the exception is properly caught with no_data_found you will return to the main block right after your select block.

Hope that helps

Adam Received on Tue May 21 2002 - 14:55:37 CEST