Re: Trasaction between procedure calls.

On Sat, 5 Jul 2008 23:35:07 -0700 (PDT), pant.nishad_at_gmail.com wrote:

>Problem Statement:
>
>I’ve written a Stored Procedure which has several insert statements.
>After inserting these statements ,its calling other stored procedure
>which again has some insert statements. Now if the second procedure
>has some error, everything should be rolled back. The skeleton of the
>procedure is as follows.
>
>
>Procedure A()
>IS
>BEGIN
> Insert into…
> Insert into…
> Insert into…
> Insert into…
> Insert into…
> Procedure B()
> Insert into…
> Procedure C()
>
>EXCEPTION
> Rollback;
> Raise application error….
>END;
>
>PROCEDURE B()
>IS
>BEGIN
> Insert into…
> Insert into…
> Insert into…
> Insert into…
> /*if error occours here.. it should rollback all the staements
>inserted in this procedure as well as the ones inserted in procedure
>A() */
>
>END;
>
>PROCEDURE C()
>IS
>BEGIN
> Insert into some different schema…..
>
>END;
>/
>
>After the errors occoured in procedure C(), its raising and
>application error which is catched by Procedure A()’s error handler,
>its executing the statement rollback, but its not actually deleting
>the records from the database.. It should rollback all the statements
>inserted in procedure A(),B() and C(). Is there any way to do it??

This question can not be answered. Apart from not including a version, you would need to post the complete procedures, so as to verify the vailidity of your exception handlers. Your exception handler in procedure A is invalid.
Typically, one propagates the exception to the top level procedure, and have this procedure rollback everything. Ignoring the invalid comment in procedure B and assuming the exception handlers in procedures B and C do contain a RAISE statement in the exception handler, your assertion no rollback is executed is incorrect.

-- 
Sybrand Bakker
Senior Oracle DBA