Re: 9i SAVEPOINT question

Jeremy wrote:
> Folks, I haven't used savepoints before. We have a large data migration
> job and what I would like to do is to set a savepoint every (say) 1000
> records so that if we should run into some error such as tablespace or
> temp segment or rollback space probs then we only lose the last (say)
> 1000 transactions.
>
> Will the following approach work:
>
> begin
> j :=0
> for sourcedata in (<select statement>)
> loop
> j := j+1;
> if j > 1000 then
> j := 1;
> savepoint mysavepoint;
> end;
>
> --
> -- do a load of time-consuming DML!
> --
>
> end loop;
> exception
> when others then
> rollback to mysavepoint;
> return;
> end;
>
>
> The real question is am I moving a pointer "further up" every time I
> issue the savepoint statement? This is the behaviour I am expecting but
> wanted to ask here for some independent comments on using this approach.
>
> Thanks all.

I think your use of savepoints is likely a bad idea. Take a look at the DBMS_RESUMABLE built-in package. You can find it documented at http://tahiti.oracle.com and a working demo in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)