Re: Execution interval is incorrect while using DBMS_JOB.SUBMIT

"Maoz Mussel" <maoz_mussel_at_yahoo.com> wrote in message news:407fd242.0408170747.43fdc0a3_at_posting.google.com...
> Hi,
>
> I've created a job using the following code:
>
> DECLARE
> X NUMBER;
> BEGIN
> SYS.DBMS_JOB.SUBMIT
> ( job => X
> ,what => 'TEST_ONLY.TEST_JOB;'
> ,next_date => to_date('17/08/2004 17:33:00','dd/mm/yyyy
> hh24:mi:ss')
> ,interval => 'TRUNC(SYSDATE+5/1440,''MI'')'
> ,no_parse => TRUE
> );
> END;
>
> ... so I expected it to run first time at 17:33:00, than again and
> again every 5 minutes. But I found that it actually run not exactly
> every 5 minutes, but with a small delay between each two execution,
> i.e. next one was on 17:38:07, then the next on 17:43:15 and so on, so
> after some time this difference have huge impact on execution time and
> intervals (the job itself is very small and completed after no more
> than a second).
> Any ides why it's happened like this? How can I change it to work as I
> expected it to be?
>
> thanks,
> Maoz

trunc (sysdate+5/1440)-> gives you midnight try it by doing select to_char(trunc(sysdate+5/1440),'mm/dd/yyyy hh24:mi:ss') from dual;

That will evaluate your expression.

What you want is sysdate+(5/1440) to get it to execute every 5 minutes. You will have some skew. You could have a table of oevery 5 minutes and have it retrieve the next one greater than the current time. Jim Received on Tue Aug 17 2004 - 11:13:47 CDT