Trasaction between procedure calls.
Date: Sat, 5 Jul 2008 23:35:07 -0700 (PDT)
Message-ID: <2d2eadef-2109-4da2-801d-774a084a9f5c@e39g2000hsf.googlegroups.com>
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 staementsinserted 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?? Received on Sun Jul 06 2008 - 01:35:07 CDT