Home » SQL & PL/SQL » SQL & PL/SQL » Transaction Management in Stored Procedures....
Transaction Management in Stored Procedures.... [message #203163] Tue, 14 November 2006 03:06 Go to next message
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 #203166 is a reply to message #203163] Tue, 14 November 2006 03:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
In your example, you have committed the transaction before calling the other procedure.
So, you can't rollback the transaction which are committed. Right?

If you want to rollback them and your business need supports, then you can commit the transaction after the procedure calls.
Also if you want to escalate the exception to the calling procedure, you can use RAISE.

By
Vamsi
Re: Transaction Management in Stored Procedures.... [message #203169 is a reply to message #203163] Tue, 14 November 2006 03:15 Go to previous messageGo to next message
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 #203170 is a reply to message #203163] Tue, 14 November 2006 03:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

AUTONOMOUS_TRANSACTION pragma
You suspend the main transaction, do SQL operations,
commit or rollback those operations, then resume the main transaction.

If that's whats your after ?
Re: Transaction Management in Stored Procedures.... [message #203360 is a reply to message #203170] Tue, 14 November 2006 21:05 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: DO We Require Index on Foreign Keys
Next Topic: passing values to the object
Goto Forum:
  


Current Time: Tue Dec 06 02:18:35 CST 2016

Total time taken to generate the page: 0.11645 seconds