| Transaction Management in Stored Procedures.... [message #203163] |
Tue, 14 November 2006 03:06  |
priya.vmr
Messages: 5
Registered: October 2006
|
Junior Member |
|
|
Hi friends,
How to implement transaction mamangement in oracle if cascading of procedures in implemented.
Hi frns,
How to implement transaction management when cascading of procedure is implemented. In the below example if i call procedure p1() it internally calls p2() and p3() and if any error occurs in p3() then the entire txns till now has to get rollbacked. How do we do that in the below.
Ex: p1()
begin
insert ...
update ..
commit
p2()
exception
rollback;
end
p2()
begin
delete ..
update ..
commit
p3();
exception
rollback;
end
p3()
begin
insert ...
commit
exception
rollback;
end
Thanks
priya
|
|
|
|
|
|
| Re: Transaction Management in Stored Procedures.... [message #203169 is a reply to message #203163] |
Tue, 14 November 2006 03:15  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You need to remove the commits that are currently in p1, p2 and p3, and add one more commit at the end of p1.
Unlike some other SQL databases, where you need to commit frequently, in Oracle, you should only commit when you've finished all the work involved in your transaction.
Personally, I'd remove the When Others exception handlers from p2 and p3. This way, if an error occurrs in p2 or p3 processing will abort, the exception will propogate back up to the exception block in p1, which will do a rollback. If no exception occurrs, then when you get to the end of P1, it will do a commit.
The other alternative is to remove all the commits and rollbacks, and determine whether the transaction has suceeded or failed at the very top leve, and commit/rollback there.
|
|
|
|
|
|
| Re: Transaction Management in Stored Procedures.... [message #203360 is a reply to message #203170] |
Tue, 14 November 2006 21:05 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
| Quote: | Unlike some other SQL databases, where you need to commit frequently,
|
just want to ask what are these other databases? why do they differ in transaction management, because as from ive learned from oracle it has a good/strict rules which will ensure data integrity, if other databases dont impelement this kind of flow arent they prone to data error? or is it just that they have a better way of managing it. thank you so much sir.
|
|
|
|
| Re: Transaction Management in Stored Procedures.... [message #203435 is a reply to message #203360] |
Wed, 15 November 2006 02:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
SQL Server is the culprit I was thinking of. It is my understanding that the implementation of row level locking and concurrency that they have requires transactions to commit frequently to avoid other sessions waiting on the transaction.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
