Re: Trasaction between procedure calls.

On Jul 6, 4:53 pm, sybra..._at_hccnet.nl wrote:
> On Sat, 5 Jul 2008 23:35:07 -0700 (PDT), pant.nis..._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

It definitely will help to have the procedure to see what its actually doing rather than scrolling through the simulated version.

Vikas Rajput Received on Sun Jul 06 2008 - 22:48:31 CDT