Re: Error Handling in Stored Procedures - Oracle 8.1.7

bcrockett_at_altavista.net (Bradley Crockett) wrote in message news:<6gLv7.53628$L8.14890132_at_news2.rdc1.bc.home.com>...
> In article <52a6e521.0110050642.26fe2f12_at_posting.google.com>,
> mycomputer1_at_hotmail.com says...
>
> >stored procedure it returns with appropriate data if data exists
> >otherwise it returns an error N0_DATA_FOUND.
>
> Trap the error in the EXCEPTION section of the procedure.
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> ...your code to handle the error...;
>
> As soon as your procedure encounters a NO_DATA_FOUND error, it will skip to the
> EXCEPTION section and run the code after the THEN. You can do whatever
> processing you want there, after which the procedure will exit and return
> control to whatever called it.
>
> Hope that helps some.

Hi,

   I have a problem related to exception handling and hence am posting it as part of this thread itself.

   I have an array of values passed to a procedure. For each element in the array, there are a series of SQL statements consisting of UPDATEs & SELECTs. The requirement is that after each element of the array is processed (at the end of the loop), I commit or rollback depending on a flag.

   If a particular value in the array caused an exception to be raised, for e.g. a NO_DATA_FOUND exception, it goes to the exception handler. After the exception handling is done, I need to go back to the procedure to continue with the next element in the array. As I understand, once control passes to the exception block, it cannot return to the procedure.

   Is there any way, I can achieve this?

Thanks.
Regards,
Ram. Received on Tue Oct 09 2001 - 11:55:17 CEST