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.

CREATE OR REPLACE FUNCTION SET_JOB_INTERVAL
  RETURN DATE
  IS
    mydate DATE := NULL;
   
BEGIN
  IF TO_CHAR(SYSDATE, 'DD') BETWEEN 1 AND 14 THEN
    mydate := TO_DATE(TO_CHAR(SYSDATE, 'MM') || '/15/' || TO_CHAR(SYSDATE, 'YYYY') ||
              ' 12:00:00', 'MM/DD/YYYY HH24:MI:SS');
  ELSE
    mydate := TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'MM') || '/01/' ||
              TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY') || ' 12:00:00', 'MM/DD/YYYY HH24:MI:SS');
  END IF;  
  
  RETURN mydate;
END;
/

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

VARIABLE job_number NUMBER
BEGIN
  DBMS_JOB.SUBMIT(:job_number, 'EXAMPLE_PROCEDURE;', SET_JOB_INTERVAL, 'SET_JOB_INTERVAL');
END;
/