Re: archiving records

Daud wrote:
> 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?

Without an order by, there is always the possibility that they are different rows. You just can't know, by definition. http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref200

>
> COMMIT;
This commit in a loop can _cause_ rollback problems. See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923 (among others).

>
> SELECT COUNT(*)
> INTO v_loop_count
> FROM MYTAB
> WHERE ROWNUM < 100001;
>
> END LOOP;
> end;
> /

jg

-- 
@home.com is bogus.
http://www.bechamel.com/v3/strange2.swf