Re: Cursors, rollback and Long Updates

User_Organization=ValleyNet <marty.himmelstein_at_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.

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.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

If at first you don't succeed, sky-diving isn't for you.