INTERVAL Problem with DBMS_JOB Package

The interval part of the job is analyzed AFTER the job is executed. If you want the job to run at the top of the hour, and you specified an interval of 'sysdate+1/24'... recognize that the value for sysdate is going to be evaluated AFTER the job completes... so if the elapsed time of the job is 5 minutes, the job will be executed at times like 1:00, 2:05, 3:10, 4:15, 5:20, etc. If you want the job to run on the hour, every hour, then you need to account for a possible delay in the start of the job, as well as the elapsed run time of the job in the value of sysdate.Just make sure that expression used for the interval results in the value that you want e.g. to schedule a job to run at the top of each hour, specify interval as 'trunc(sysdate,''HH24'')+1/24'.

Examples:

Run every day at 8.00 in the morning:

dbms_job.submit(:jno,'xyz;',trunc(sysdate)+1/3,'trunc(sysdate+1)+1/3') ;

Example For Choosing date & time:

---------------------------------

select trunc(sysdate+1)+1/24*8 "08:00 AM",

trunc(sysdate+1)+1/24*9 "09:00 AM",

trunc(sysdate+1)+1/24*10 "10:00 AM"

from dual;

Sample Output:

==========

08:00 AM                          09:00 AM                             10:00 AM

-----------                          -----------                             -----------

4/20/2010 8:00:00 AM       4/20/2010 09:00:00 AM       4/20/2010 10:00:00 AM

For more see the following link :

http://www.pafumi.net/dbms_job.htm