Originally posted by gpsingh
Hi,

The interval is the interval between the job run and next run time.
You cannot set the time it should run but after how much time it should run after job execution.
So if you want it to run at 7AM ,u have to execute it at 7AM
and then give trunc(sysdate) + 1.

I hope it is clear.
You are wrong. If you set the job to run today at 7AM and set the interval to 'trunc(sysdate) + 1', your job will run at midnight every day, not at 7AM. You could set the interval to 'sysdate+1', which means it will run tomorow and any other day at *approximately* 7AM. Why approximately? Today it might happen to execute 30 seconds past 7AM. So when the job finishes today, tje next execution date will be set at tomorow at 07:00:30AM. Again, tomorow the job scheduler might be late for another 30 seconds, resulting at execution time at 07:01:00AM. And so on and on, the start time of the job will slowlly be shifted and in a couple of months you'll find it starting at 8AM instead of at 7AM.

To avoid this, you have to "anchore" the next starting time of the job, which is exactly what the chintz's original call does: "dbms_job.interval(xyz,'TRUNC(SYSDATE+1)+7/24');" This ensures that the job will be executed at 7AM every day. It might actualy start a couple of secunds later (depending on your JOB_QUEUE_INTERVAL setting), but the the time offset will not accumulate!

Chintz, again, your syntax is perfectly correct, I'm 100% sure. You say you are seeing NEXT_SEC field as set to 00:00:00. But this is normal! You have only set the new interval, not the new time of your first next execution time. To change your next execution time, you must use DBMS_JOB.NEXT_DATE(xyz,TRUNC(SYSDATE)+14/24); if you want to execute it today at 2PM for example. But tomorow it will be run at 7AM!