Re: What happens after an error in an PL/SQL proc?

In our last gripping episode mitch23_at_hotmail.com wrote:
> I have a return parm in an Oracle 8.0 stored procedure that indicates
> whether any errors occurred in the proc. I have (very) simple
> exception handler that returns a 1 in the return parm if the proc
> errors and then terminates execution. I had no compilation errors but
> I wanted to make sure that the following code will have the desired
> effect. If an error occurs, will Oracle terminate execution of the
> proc or will it return to the FOR...LOOP and continue processing?
>
> Thanks much!
> =====================================================
> CREATE PROCEDURE UPDATE_LOAN(SUCCESS OUT NUMBER) IS
>
> CURSOR LOANS IS
>
> SELECT A.CLIENT, A.LOAN
> FROM ALODETAIL A, ALOLOAN C WHERE A.LOAN=B.LOAN
>
> tmpBalance NUMBER(30,3) := 0.00;
>
> BEGIN
>
> SUCCESS := 2;
>
> FOR rs IN loans loop
>
> SELECT SUM(A.AMOUNT) INTO tmpBalance FROM ALODETAIL A WHERE
> LOAN=rs.LOAN;
>
> UPDATE ALOLOAN SET AMOUNT_6=(tmpBalance) WHERE LOAN=rs.LOAN;
>
> COMMIT;
>
> END LOOP;
>
> EXCEPTION
> WHEN OTHERS THEN
> SUCCESS := -1;
> END;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Once Oracle encounters an error in PL/SQL it will look for an exception handler for that error (or exception), process that handler (if one exists) and exit. If no handler exists the procedure will simply exit.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.