Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Update inside cursor loop
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
>
As others have said you are missing an update in your loop. Much simpler
however would be a simple update statement no?
SQL> drop table t;
Table dropped.
SQL> create table t(fuelfilldate number,fuel_fill_dt2 date);
Table created.
SQL> insert into t values (12012006,null);
1 row created.
SQL> insert into t values(7042006,null);
1 row created.
SQL> commit;
Commit complete.
SQL> update t
2 set fuel_fill_dt2 = to_date(lpad(fuelfilldate,8,0),'ddmmyyyy');
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from t;
FUELFILLDATE FUEL_FILL
------------ --------- 12012006 12-JAN-06 7042006 07-APR-06
SQL> you obviously might want a where clause in the real case...
-- Niall Litchfield Oracle DBA http://www.orawin.info/servicesReceived on Thu Apr 12 2007 - 10:02:32 CDT