Re: Need advise from PL/SQL experts on script

Thank you all for your advice.

What my first intention was to try to avoid 'snapshot too old' problem and also rollback segment extents error as this update involves millions of rows. Dave, you are right, I did try to use your technique and of course, it didn't work for my type of updates.

I end up by narrowing it down to a selection base on a column use for categorizing the customers and do the update base on that selection using sql script. Time consuming no doubt but I am new to pl/sql so better be sure than making mistake.

Thanks again.

"LGold" <leighlhg_at_home.com> wrote in message news:y5oi6.3141$RU.116774_at_news1.rdc1.ab.home.com...
> Hi there,
>
> 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;
> /
>
> Thanks.
>
>
>
>
Received on Sat Feb 17 2001 - 11:20:10 CST