Re: Update inside cursor loop

On 11 Apr 2007 14:12:59 -0700, dwmccay_at_southernco.com wrote:

>My table has a NUMBER field that has been used as a date field (setup
>by someone else). 12012006 would be 12/01/2006, 7042006 would be
>7/04/2006. I have recently added a DATE field to the table. I am
>trying to write something that will take the NUMBER, re-format it, and
>UPDATE the DATE field in the same record. I figured out how to slice
>up the number into month day and year chunks with the SUBSTR function
>as long as I do 8 digit and 7 digit numbers seperately. Running two
>versions of the program will not be a problem. This is what I have so
>far:
>
>declare
> cursor d1 is
> select fuelfilldate, fuel_fill_dt2 from lincbase.genrawdata
>where fuel_fill_dt2 is null and length(to_char(fuelfilldate)) = 8
> for update of fuel_fill_dt2;
>begin
> for gen_rec in d1 loop
> gen_rec.fuel_fill_dt2 :=
>to_date(substr(gen_rec.fuelfilldate,-8,2) || '/' ||
>substr(to_char(gen_rec.fuelfilldate),-6,2) || '/' ||
>substr(to_char(gen_rec.fuelfilldate),-4),'mm/dd/yyyy');
>end loop;
>end;
>/
>
>It runs without errors however it does not appear to actually do
>anything. The fuelfilldate field is the NUMBER, the fuel_fill_dt2 is
>the DATE field.
>
>Thanks,
>
>Danny

It is always
select for update

update lincbase.genrawdata
...
where current of d1;

Actually you don't need the for loop at all. There is no reason why a single update statement wouldn't suffice.

Hth

-- 


Sybrand Bakker
Senior Oracle DBA