Re: commit or not commit inside a cursor loop

ctcgag_at_hotmail.com wrote:
: andyho99_at_yahoo.com (Andrew) wrote:
: > Hi,
: >
: > I need to update a table with couple million rows. For some reason, I
: > have to use pl/sql instead of SQL to do the job. My understanding is
: > that I put COMMIT after the loop if rollback segment is big enough. If
: > I worry the rollback segment, I'll commit every 20,000 rows inside the
: > loop (and after the loop of course).
: >
: > However, I read pl/sql document the other day. It says,
: > -------------------------------------------------------------
: > DECLARE
: > CURSOR c1 IS SELECT ename, job, rowid FROM emp;
: > my_ename emp.ename%TYPE;
: > my_job emp.job%TYPE;
: > my_rowid UROWID;
: > BEGIN
: > OPEN c1;
: > LOOP
: > FETCH c1 INTO my_ename, my_job, my_rowid;
: > EXIT WHEN c1%NOTFOUND;
: > UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;
: > -- this mimics WHERE CURRENT OF c1
: > COMMIT;
: > END LOOP;
: > CLOSE c1;
: > END;

: So this process gets half way through and the server dies or you get
: a 1555 or space allocation error or something like that. Now half the
: people already got their raises, the other half didn't, and you have no
: reliable way to know who did or who didn't. Isn't that cheerful?

job takes 48 hrs.

at 47 hrs, 57 minutes, problem arises causing the entire job to fail. Next day try again - and again - and again - nobody every gets a raise.

Perhaps the code should simply record what has been done and include that in the commit.

	UPDATE emp 
	SET sal = sal * 1.05
	  , last_updated = sysdate
	WHERE 
	    last_updated < last_run_date

(Ignoring some details) Received on Wed Jun 11 2003 - 00:46:24 CDT