Re: time shift in sheduled job

In our last gripping episode buckeye714_at_my-deja.com wrote:
> In article <G3B640.7Jw_at_news.boeing.com>,
> "James" <jialong.x.xie_at_boeing.com> wrote:
> > I need to run the scheduled job every two hours. So far so good.
 But I
> > notice that there is a problem because the next_date has been
 shifting
 from
> > the sharp o'clock after a few runs. For example, if I start the job
 at
 12:00
> > noon, the next job will be running at about 2:01, the next one may
 be
 4:02.
> > I can see that this will become serious after a few months. I can
 re-submit
> > the job after a few days. But is there any better way to reset the
 next_date
> > to sharp o'clock? Thanks in advance for any clue.
> >
> > Here is the script:
> >
> > BEGIN
> > DBMS_JOB.ISUBMIT
> > (1,'MY_PROC;',TRUNC(SYSDATE)+12/24,'SYSDATE+1/12',NULL);
> > COMMIT;
> > END;
> >
> >
> I haven't tested this, so I don't know if it is entirely accurate.
 But,
> I do know that next_date and next_sec are calculated using the value
> specified in interval and the time the job ENDED, not when it began.
> So, my assumption is that the job runs in less than an hour. Try
 using
> the following specification for interval.
>
> 'to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12'
>
> I believe this will add 2 hours to the 'hour' when the job ended,
> ignoring the value of 'minutes' altogether.
>
> HTH,
> Patrick
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You are accurate about the next time calculation however your solution will not work as it returns an error from SQL*Plus:

SQL> select to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12   2* from dual
SQL> /
ERROR:
ORA-01722: invalid number

no rows selected

As I stated in a previous post the time shift constant will need to be slightly less than 1/12 to compensate for the run time of the job.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.