Trasaction between procedure calls.
in [Oracle]
|
From: pant.nishad on 6 Jul 2008 02:35 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??
From: sybrandb on 6 Jul 2008 07:53 On Sat, 5 Jul 2008 23:35:07 -0700 (PDT), pant.nishad(a)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
From: Vikas Rajput on 6 Jul 2008 23:48 On Jul 6, 4:53 pm, sybra...(a)hccnet.nl wrote: > On Sat, 5 Jul 2008 23:35:07 -0700 (PDT), pant.nis...(a)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
|
Pages: 1 Prev: create two database Next: LIKE operator on CHAR column when there is no wildcard character |