Re: Need advise from PL/SQL experts on script

LGold wrote in message ...
>I need some advise from any PL/SQL expert regarding the PL/SQL script below.
>What I am trying to accomplish is to change customer number in a table to a
>new number. This new number is stored in a mapping table that consists of 2
>columns, one stores the old customer, and the other, the new customer
>number. I am new in PL/SQL and would like comments on the script below. My
>concern is if I have a mapping table with data like this (see below), will
>the conversion be done twice to the row that has old customer number of 333?
>If it has been converted to 456, will it run the risk of being converted to
>777 after that?
>
>map table structure - old_cust, new_cust
> 333 456
> 444 457
> 456 777
>
>DECLARE
> cursor c1 is select a.rowid, b.new_cust from customer a, map_table b,
> where b.old_cust = a.cust_num;
> c1_rec c1%ROWTYPE;
> commit_cnt number := 0;
>
>BEGIN
> open c1;
> LOOP
> fetch c1 into c1_rec;
> exit when c1%NOTFOUND;
> update customer set cust_num = c1_rec.new_cust
> where rowid = c1_rec.rowid;
>
> commit_cnt := commit_cnt + 1;
> if commit_cnt > 20000 then
> commit;
> commit_cnt := 0;
> close c1;
> open c1;
> end if;
> END LOOP;
> close c1;
> commit;
>END;
>/

Yes, the row will be updated twice. More than that, your program will be stuck in a loop - it will update the "first" 20000 rows for ever, as each time you re-open then cursor you start again at the beginning.

I assume you're closing and re-opening the cursor to avoid "snapshot too old"? (It's a technique I suggest on my web-page: http://home.clara.net/dwotton/dba/snapshot.htm ), but, as I describe there, this technique won't work for this sort of update. One option is to change the cursor so that it reads through the customer table in some sequence (probably best not customer number, in your case) and each time it re-opens the cursor, to restart from the point where it left off. Another approach is to create a 'last-updated' column on the table and update this as well with a fixed time-stamp as you go along. Change the cursor so that it only selects rows with the 'last-updated' column not equal to your fixed time-stamp. That way rows will only be updated once, and you will eventually get through the whole lot.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.