From: Marty Himmelstein <marty.himmelstein@valley.net>
Subject: Re: Cursors, rollback and Long Updates
Date: 1995/08/08
Message-ID: <406krf$ceo@dartvax.dartmouth.edu>#1/1
references: <3vi8c6$1cov@news-s01.ca.us.ibm.net> <3vj3a4$urs@dartvax.dartmouth.edu> <4010j3$o0e@ixnews2.ix.netcom.com>
to: chuckh@ix.netcom.com
content-type: text/plain; charset=us-ascii
organization: ValleyNet
mime-version: 1.0
newsgroups: comp.databases.oracle
x-mailer: Mozilla 1.1N (Windows; I; 16bit)


chuckh@ix.netcom.com (Chuck Hamilton) wrote:
>Marty Himmelstein <marty.himmelstein@valley.net> wrote:
>
>>Simon,
 
>>I recently had a similar problem.  This is how a solved it:
 [snippsville]
>>5. Every x transactions commit the data, and, in a separate activity log table, 
>>record the last rownum processed by the program. I also use the activity log table 
>>to record timings, and monitor progress of the stored procedure, for performance 
>>tuning.
 
>>If the stored proc fails in the middle, look at the activity log to figure out at 
>>what row to restart.
>
>But what if it fails somewhere in between updates to the activity log?
>Aren't you going to try to reprocess rows that were already processed.
>
No. Each time I issue a commit for the "real" data, I update the activity_log table.

>I would suggest a nearly identical process, except I'd trap any error
>condition that causes the PL/SQL block to fail and write out the
>rownum it failed on before exiting. That'd make recovery a little
>smoother. And unless you're interested in checking the progress of the
>program while it's running, you could probably speed it up even
>further but not writing the activity log at all until the very end.

The performance hit for writing to the activity log is noise.  I keep a running 
counter in a local variable, and just write it to the database at commit time.  My 
procedure is parameterized so that I can run just 5,000 inserts, for example, even 
if there are 1,000,000 rows to insert.  I do this for performance tuning, and 
debugging.  I do incorporate your suggestion of logging error conditions.

Marty Himmelstein
marty.himmelstein@valley.net