RE: set based transaction management; SAVEPOINTS

  • From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
  • To: cosmini@xxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 12 Jul 2007 10:57:13 -0500

Oracle does support rollback to savepoints, etc, but it doesn't sound to
me like you need them.  Just start a new transaction at the start of
each iteration of the loop.  Something like, begin transaction, call
proc, end transaction. Or am I missing something?


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cosmin Ioan
Sent: Thursday, July 12, 2007 10:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: re: set based transaction management; SAVEPOINTS


hello all,
one more quandary and I'll try to illustrate it by a small example:
A big procedure (in a package), PROC_A is executed from Java.  I want
(at the)  Java (level) to  determine the  COMMIT/ROLLBACK (at the very
end) or commit every X records.

PROC_A  contains a LOOP on possibly hundreds or thousands of iterations.
Each iteration can contain multiple DML operation, and each of these DML
operation can fail (select not found, record not updated, insert key
violation, etc)

How can I code this transaction management -- through (dynamic
SAVEPOINTS? -- is there such a thing?)  so that when (and if transaction
management, the commit is done only at the very end of PROC_A) any one
of these DML's fail, then I roll back **all of the operations within
that specific iteration only** (but previous or subsequent successful
iterations get committed).

any thoughts?
thx a bunch,

NOTICE:  This electronic mail message and any attached files are confidential.  
The information is exclusively for the use of the individual or entity intended 
as the recipient.  If you are not the intended recipient, any use, copying, 
printing, reviewing, retention, disclosure, distribution or forwarding of the 
message or any attached file is not authorized and is strictly prohibited.  If 
you have received this electronic mail message in error, please advise the 
sender by reply electronic mail immediately and permanently delete the original 
transmission, any attachments and any copies of this message from your computer 
system. Thank you.


Other related posts: