specifying time in dbms_job 1

[Jami]

I am trying to use dbms_job to run a procedure daily. I can do this in whole hours, but want to run the procedure at 1:55 daily or even 1:30 daily. I am unsure how to specify minutes of the day. I tried the code below, but this was not running and have tried other variations, but have always gotten compilation errors. Can anyone help?

SQL> declare
2 itt_jobnum NUMBER;
3 BEGIN
4 dbms_job.submit(itt_jobnum, 'itt_fixed_file;', SYSDATE,
5 'trunc((sysdate) + 1) + 1355/24');
6 commit;
7 end;
8 /

Using 13/24 will run it at 1. But how can I run this at 1:55? Thanks!
Jami

 

[karluk]

In general Oracle dates assume that adding 1 means adding one day. Adding 13/24 of a day gets you to 1pm. 24*60=1440 is the number of minutes in a day, so adding another 55/1440 will get you to 1:55pm. The total is 13/24+55/1440, which simplifies to 835/1440 if I have done all the math correctly.

Therefore your dbms_job command should be the following. Please note the logic. "trunc(sysdate+1) gets you to midnight and the extra 835/1440 is an offset to get to the exact minute tomorrow.

SQL> declare
2 itt_jobnum NUMBER;
3 BEGIN
4 dbms_job.submit(itt_jobnum, 'itt_fixed_file;', SYSDATE,
5 'trunc((sysdate) + 1) + 835/1440');
6 commit;
7 end;
8 /