Setting Complex Oracle Job Intervals

It may not be immediately apparent, but DBMS_JOB.SUBMIT permits the use of function calls to set the time interval for executing an Oracle job. This allows for more complex control over the interval rather than attempting to calculate the appropriate interval using SYSDATE math.

Here we have a simple function which returns either the 15th day of the current month or the 1st of the subsequent month (whichever comes first from the current date). This function is intended to set an interval where the job is executed on the first and fifteenth of each month at Noon. I think this would be relatively complicated and unreadable to try to set using just SYSDATE math as an argument in the DBMS_JOB.SUBMIT call.

The following code submits a new job for execution. Note that the last two arguments in the call refer to the function defined above.

Leave a Comment