OracleHere’s a way to force an Oracle date calculation to find a specific time of day every time. This is useful when using the DBMS_JOB package for job scheduling jobs as it gives you full control over the time of day a job runs.

The Oracle date data type is accurate down to the second, but if you want to ignore this precision and get to a certain time you can use the TRUNC function to truncate the date down to just the day, then add as many hours or minutes as you want.

By default the TRUNC command will truncate a date and time to midnight on the start of that date, so to consistently get, for example, 2:00 pm, we can truncate the time and add 14 hours.

SQL> SELECT to_char(trunc(sysdate) + 14/24, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/13/2006 02:00 PM

Here we truncate to midnight and add 14/24 of a day (14 hours). This works on the idea that the default increment for math with oracle dates is 1 day, so if we add 14/24 days we’re adding 14 hours. You could also use the NUMTODSINTERVAL function as described here, but it makes the command longer.

Applying this to scheduled jobs

When scheduling jobs with the DBMS_JOB package you specify the interval with a string which evaluates to an Oracle date. The interval is evaluated every time a job runs to calculate the next time the job should run. Typically the interval is something like 'SYSDATE + 1' if you want the job to run once a day.

The interval is evaluated each time the job starts. If the job runs at 11:00 PM, the interval would evaluate to 11:00 pm the following day; however, if you force the job to run with the DBMS_JOB.RUN at 2:30 pm, the interval would be evaluated to 2:30 pm the next day and you have changed your job to run at 2:30 every day thereafter.

To make sure things always run at the same time we can apply the method above to the interval when we submit the job.

SQL> BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'TRUNC(SYSDATE + 1) + 23/24';
END;
/

This would update job number 21 to use the string 'TRUNC(SYSDATE + 1) + 23/24' to calculate the next time this job should run. In this case, we take SYSDATE, plus 1 day, truncate it to midnight of that day, and add 23 hours. The end result is, no matter what time the job is run the interval will evaluate to 11:00 pm the next day.

oracle, database, dba, database programming, database administration, sql, plsql, pl/sql