This discussion is archived
1 Reply Latest reply: Dec 4, 2012 11:27 AM by spajdy RSS

dbms_scheduler Job and report time conflict

792290 Newbie
Currently Being Moderated
Hello,
We have scheduled some Jobs such as monthly, semi-annually reports using dbms_scheduler. we use 10gR2 software and windows 2003 server. we have a report which supposed to run at 4:00 AM and as soon as the report finishes it sends the report as email to the authorized users. when the scheduler ran the report inside the report it shows as ran at 3:08 AM where they get systimestamp. but when i query the

select last_stat_time, next_run__date from dba_scheduler_jobs where job_name ='TEST';

last_start_time next_run_date
03-DEC-12 04.00.00..223000 AM -04:00 07-JAN-13 04.00.00..200000 AM -04:00

when I query systimestamp from dual on that database from sqlplus:
i get the following:
systimestamp
04-DEC-12 07.40.16..152000 AM -05:00

I also ran the default_timezone setting attribute to 'US/Eastern';
what else need to set to automatically correct when daylight savings starts and ends? do i have to specify in the start_time of the Job attribute anything? here is myy job script:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(JOB_NAME => test.testREP',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION=> 'BEGIN test.monthly_rep(); END;',
START_DATE => SYSDATE,
REPEAT_INTERVAL=> 'FREQ=monthly; bysetpos=1; byday=MON;BYHOUR=04; BYMINUTE=00; BYSECOND=00; ',
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Monthly rep');
END;
/

I see the difference of -04.00 and -05.00 from both of the queries. i know the systimestamp from dual is correct. how to I fix the scheduler Job to run at the correct time with daylight savings to take effect?


Help is much appreciated.

Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points