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; /