Re: Atomically moving rows from one table into another

I use a cursor in a pl/sql procedure taking advantage of rownum:

create or replace procedure p is
  cursor c1 is select f1, f2, f3, ROWID r from t; begin
  for c in c1 loop
    insert into T2 values (c.f1, c.f2, c.f3);     delete from t where rowid = c.r;
    commit; -- commits both the insert and the delete   end loop;
end;

If another session inserts data into T, it will not have a rowid contained in your cursor's rowids.

another way is to use FOR UPDATE in your cursor and DELETE WHERE CURRENT OF, but you cannot use commits in this scenario until the cursor is closed

"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns929DB035AAFC0wrgrpde_at_130.133.1.4...
> Hello,
>
> suppose I have two tables, tab1 and tab2, created from the same DDL.
> I want to move data from tab1 to tab2 like this: (xxx is some
> where clause)
>
> INSERT INTO tab2 (SELECT * FROM tab1 WHERE xxx);
> DELETE FROM tab1 WHERE xxx;
> COMMIT;
>
> Now, what happens if, after the INSERT yet before the DELETE,
> someone inserts rows into tab1 that match xxx? They will be
> deleted although they have not been transferred to tab2.
>
> Is there a way to atomically move data from one table to
> another that doesn't suffer from this problem?
>
> Thanks for any help
> W. Roesler
Received on Fri Oct 04 2002 - 12:11:13 CDT