date format

[lgaorcl]

Can anybody help me with this?


INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,to_date(fdate,'MM/DD/YYYY'),weektype,gid);

Oracle gives me an error. Here's what I am trying to do. Find out how many weeks between the sdate and fdate then duplicate data based on mdate between the 2 dates. If I hardcode cnt, I can get it to insert the values, though I still need to figure out why I can't format the date model in the insert statement. I need something like this:

INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,to_date(fdate,'MM/DD/YYYY')+(7*cnt),weektype,gid);

Each of the dates are for every month of the month. Table schema is here:
Thanks.




CREATE TABLE HOURLY_MP_WEEK1 (
ID NUMBER (5) NOT NULL,
WEEK DATE NOT NULL,
WEEK_TYPE VARCHAR2 (2) NOT NULL,
GROUP_ID NUMBER (2),
CHECK (week_type in ('D','Sa','Su')),
CONSTRAINT PK_HOURLYMPWEEK1
PRIMARY KEY ( ID )
USING INDEX
TABLESPACE USERS PCTFREE 10
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 50 ))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10240
NEXT 10240
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 121
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;


insert into hourly_mp_week1 (id,week,week_type,group_id) values (200,'11/05/2001','D',1);
insert into hourly_mp_week1 (id,week,week_type,group_id) values (201,'11/05/2001','Sa',1);
insert into hourly_mp_week1 (id,week,week_type,group_id) values (202,'11/05/2001','Su',1);



CREATE OR REPLACE PROCEDURE REPLICATE_DATA(
gid number,
mdate date,
fdate date,
tdate date)
AS
id hourly_mp_week1.id%TYPE;
weektype hourly_mp_week1.week_type%TYPE;
cnt number(2);
i number;
CURSOR c1 IS
SELECT id,week_type FROM hourly_mp_week1
WHERE to_char(week,'MM/DD/YYYY')='11/05/2001';
CURSOR c2 IS
SELECT to_number(to_char((to_date('12/17/2001','MM/DD/YYYY')-to_date('12/03/2001','MM/DD/YYYY'))/7)) FROM dual;

BEGIN
OPEN c2;
LOOP
FETCH c2 INTO cnt;
EXIT WHEN c2%NOTFOUND;
END LOOP;
CLOSE c2;

OPEN c1;
LOOP
FETCH c1 INTO id,weektype;
EXIT WHEN c1%NOTFOUND;

FOR i IN 0..cnt LOOP
/* need to vary the fdate also, but I'm still working on it */
INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,fdate,weektype,gid);
END LOOP;

END LOOP;
CLOSE c1;
COMMIT;

END REPLICATE_DATA;

[marist89]

It's not clear exactly what you are trying to do, but I think an example is in order:

SQL> create table xyz (x date);
Table created.

  1  declare
  2     i integer;
  3  begin
  4     for i in 1..10 loop
  5        insert into xyz values (sysdate+i);
  6     end loop;
  7     commit work;
  8* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from xyz;

X
---------
18-OCT-01
19-OCT-01
20-OCT-01
21-OCT-01
22-OCT-01
23-OCT-01
24-OCT-01
25-OCT-01
26-OCT-01
27-OCT-01

10 rows selected.

SQL>

[prafful]

What is the exact error you are getting?