Re: set based transaction management; SAVEPOINTS

Cosmin

You can execute something like this:

for x in (select y from z where ...)
loop
   -- do processing

   rowcount := rowcount + 1

   if processing_by_batch then
      if error_was_detected then
          rollback; -- this discards changes in this iteration only
      elsif rowcount % 1000 = 0 then 
          commit;
      end if;
   end if;
end loop;


If you are using batch fetches (bulk collect) then you can simply commit after every bulk iteration. 

Handling errors: when you detect an error, set the error_was_detected boolean. All your DML should be within:
  if NOT error_was_detected then
     ... perform updates
  end if;

to minimise the amount of rollback required (essentially you just spin through the rest of the batch, and don't restart DML until the beginning of the next batch.

Note that commits within a PL/SQL block are somewhat "special" - ie commit write nowait. See Christo Kutrovsky's explanation of that here: http://www.pythian.com/blogs/162/quantifying-commit-time. 


BUT: because you are selecting across commits - so you may suffer ORA-1555 (snapshot too old) on the main select (ie it can't reconstruct a read-consistent view). That really depends whether your processing is directly impacting on the data you are selecting. 

Regards Nigel


----- Original Message ----
From: "Kerber, Andrew W." <Andrew.Kerber@umb.com>
To: cosmini@bridge-tech.com; oracle-l@freelists.org
Sent: Thursday, July 12, 2007 4:57:13 PM
Subject: RE: set based transaction management; SAVEPOINTS


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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Cosmin Ioan
Sent: Thursday, July 12, 2007 10:38 AM
To: oracle-l@freelists.org
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,
Cos




------------------------------------------------------------------------------
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.

==============================================================================

--
http://www.freelists.org/webpage/oracle-l