archiving records

From: Daud <daud11_at_hotmail.com>
Date: 27 Aug 2006 21:58:05 -0700
Message-ID: <1156741084.960790.248830@75g2000cwc.googlegroups.com>

I just saw this piece of code written by one developer to archive records from one table to another table. The code has, of course, been simplified. He is doing it 100K rows at a time to avoid using too much rollback, I think. But what I am not sure is whether the 100K rows that got inserted in the archive table are the same as those that later got deleted.
Please advised.

Daud

---

declare

        v_loop_count integer;
begin

       SELECT COUNT(*)
       INTO v_loop_count
       FROM MYTAB
       WHERE ROWNUM < 100001;

       WHILE (v_loop_count > 0) LOOP

           INSERT INTO MYTAB_ARC
           SELECT * FROM MYTAB WHERE ROWNUM < 100001';

           DELETE FROM MYTAB WHERE ROWNUM < 100001;
           -- have i deleted the same 100K rows that just got inserted?

           COMMIT;

           SELECT COUNT(*)
           INTO v_loop_count
           FROM MYTAB
           WHERE ROWNUM < 100001;

       END LOOP;

end;
/ Received on Sun Aug 27 2006 - 23:58:05 CDT