I have a procedure named MY_PROCEDURE_X in a packaged MY_PACKAGE_X. My requirement is that the procedure need to be executed on 1st and 16th of every month. If it is running on 1st of the Month, then the time of execution should be 10:00 AM, If it is running on 16th of the Month, then the time of execution should be 05:00 PM.
Can I make a single job to do this both? Below is my half done script:
BEGIN
dbms_scheduler.create_job (
job_name => 'PROCESS_MY_JOB_X',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'MY_PACKAGE_X.MY_PROCEDURE_X',
START_DATE => TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI'),
repeat_interval => 'FREQ=DAILY; INTERVAL=14',
ENABLED => TRUE,
comments => 'RUN JOB ON 1ST AND 16TH OF EVERY MONTH');
END;
/
Thanks in advance ;)
EDIT: My previous answer did not actually work. BYHOUR could not be used in the named schedule for some reason - it gave an error.
Instead I discovered a way to do it in a single repeat_interval
expression:
'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
The trick here is that BYMONTHDAY=1,16 and BYHOUR=10,17 actually creates a set of four date/times:
The 1st at 10, the 1st at 17, the 16th at 10, the 16th at 17
Then BYSETPOS=1,4 picks the 1st and 4th date/times out of the set of four, and that is the two date/times we want.
One can always test a repeat_interval
expression using dbms_scheduler.evaluate_calendar_string
like for example:
declare
startdate date;
dateafter date;
nextdate date;
begin
startdate := TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI');
dateafter := startdate;
for i in 1..24 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
, startdate
, dateafter
, nextdate
);
dbms_output.put_line(to_char(nextdate,'YYYY-MM-DD HH24:MI'));
dateafter := nextdate;
end loop;
end;
/
That block outputs this result:
2014-11-16 17:00
2014-12-01 10:00
2014-12-16 17:00
2015-01-01 10:00
2015-01-16 17:00
2015-02-01 10:00
2015-02-16 17:00
2015-03-01 10:00
2015-03-16 17:00
2015-04-01 10:00
2015-04-16 17:00
2015-05-01 10:00
2015-05-16 17:00
2015-06-01 10:00
2015-06-16 17:00
2015-07-01 10:00
2015-07-16 17:00
2015-08-01 10:00
2015-08-16 17:00
2015-09-01 10:00
2015-09-16 17:00
2015-10-01 10:00
2015-10-16 17:00
2015-11-01 10:00