I am unsure how to tackle using Oracle DBMS_SCHEDULER. I have been asked to run a particular PL/SQL procedure job at:
8AM, 12PM, 4PM and lastly at 8PM every day of the week
and then re-start again the following day at the same times again at: 8AM, 12PM, 4PM and lastly at 8PM.
I understand that I firstly need to create a schedule but don’t know what the frequency setup should be, i.e.:
dbms_scheduler.create_schedule( schedule_name => 'MY_JOB',
repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=00'
start_date => SYSTIMESTAMP
);
You don't have to create a named SCHEDULE (but you can do if you prefer), you can put the repeat interval directly in the job:
DBMS_SCHEDULER.CREATE_JOB (
job_name => ...
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00'
Note, for jobs with frequency less than daily you have to consider daylight saving times. Time zone of SYSTIMESTAMP is the time zone of database server's operating system, very often this is set as static UTC offset (e.g. +02:00). In order to take daylight saving times into account this UTC offset changes twice a year.
If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date. For example you can do
DBMS_SCHEDULER.CREATE_JOB (
job_name => ...
,start_date => SYSTIMESTAMP AT TIME ZONE 'Europe/Zurich'
,repeat_interval => 'FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00'
Have a look at this PL/SQL block to see the difference.
DECLARE
next_run_date TIMESTAMP WITH TIME ZONE;
start_date TIMESTAMP WITH TIME ZONE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Static UTC offset:');
start_date := TIMESTAMP '2019-10-26 00:00:00 +02:00';
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00', start_date, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE(next_run_date AT LOCAL);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Time zone region name:');
next_run_date := NULL;
start_date := TIMESTAMP '2019-10-26 00:00:00 Europe/Zurich';
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00', start_date, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE(next_run_date AT LOCAL);
END LOOP;
END;
Static UTC offset:
2019-10-26 08:00:00.000 +02:00
2019-10-26 12:00:00.000 +02:00
2019-10-26 16:00:00.000 +02:00
2019-10-26 20:00:00.000 +02:00
2019-10-27 07:00:00.000 +01:00
2019-10-27 11:00:00.000 +01:00
2019-10-27 15:00:00.000 +01:00
2019-10-27 19:00:00.000 +01:00
2019-10-28 07:00:00.000 +01:00
2019-10-28 11:00:00.000 +01:00
Time zone region name:
2019-10-26 08:00:00.000 +02:00
2019-10-26 12:00:00.000 +02:00
2019-10-26 16:00:00.000 +02:00
2019-10-26 20:00:00.000 +02:00
2019-10-27 08:00:00.000 +01:00
2019-10-27 12:00:00.000 +01:00
2019-10-27 16:00:00.000 +01:00
2019-10-27 20:00:00.000 +01:00
2019-10-28 08:00:00.000 +01:00
2019-10-28 12:00:00.000 +01:00
See Repeat Intervals and Daylight Savings for more details