Update inside cursor loop

From: <dwmccay_at_southernco.com>
Date: 11 Apr 2007 14:12:59 -0700
Message-ID: <1176325979.567033.106000@p77g2000hsh.googlegroups.com>

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 Received on Wed Apr 11 2007 - 16:12:59 CDT