9i SAVEPOINT question

From: Jeremy <jeremy0505_at_gmail.com>
Date: Thu, 24 Nov 2005 11:58:56 -0000
Message-ID: <MPG.1defbb4dd0bff65898a037@news.individual.net>

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.

-- 
jeremy

We use Oracle 9iR2 on Solaris 8 with the Oracle HTTP Server and 
mod_plsql

Received on Thu Nov 24 2005 - 05:58:56 CST