Commit in a procedure [message #7477] |
Tue, 17 June 2003 01:25 |
Jayasri
Messages: 28 Registered: July 2000
|
Junior Member |
|
|
Hi,
Could you please help me out with the following problem.
I have two procedures P1, P2. P2 is called in P1 as follows.
PROCEDURE P1 IS
.......
P2;
.......
COMMIT;
END; ( END OF PROCEDURE P1)
PROCEDURE P2 IS
.........
COMMIT;
END;
When I call procedure P2 in P1, commit in P2 will commit all the transactions that are made till this call in P1. The control comes to P1 once the commit is complete in P2.
Now my question is, if something goes wrong in P1, can I rollback the transaction that has been committed in P2. This question has been asked in an interview. Interviewer says it is possible to rollback. I don't know how. Is it possible with the help of PRAGMAs.
Regards
Jayasri.
|
|
|
Re: Commit in a procedure [message #7479 is a reply to message #7477] |
Tue, 17 June 2003 01:47 |
Deep
Messages: 15 Registered: July 2001
|
Junior Member |
|
|
U can rollback only the uncommited changes.
Once u commit inside the p2, then it cannot be rolled back in p2 itself.
But if u are not using a commit in p2, then u can rollback the changes done in p2 by giving a rollback in p1.
But when u use pragma autonomous transaction then, whatever commit or rollback u do in p2 will not be reflected in the p1 procedure and vice versa.
|
|
|
|
|