How to accomplish this job using Oracle 19c schedule?

user13620045

I need to create a job to run at 11pm on the 7 and 22 days of each month. But if they are Saturday, then run the job a day ahead on Friday. If they are Sunday, then run the job on the next Monday. Thanks.

GregV

Hi,

One possibilty to get this schedule is to create 3 named schedules:

• One named schedule called "sched_7_22_if_sat" that will find the 7th and 22nd days that are falling on Saturday and in which case you take the preceding day Friday (hence the -1d in each value of the BYDATE clause)
• One named schedule called "sched_7_22_if_sun" that will find the 7th and 22nd days that are falling on Sunday and in which case you take the following day Monday (hence the +1d in each value of the BYDATE clause)
• One named schedule called "sched_7_22" that will find the 7th and 22nd days that are falling on week days, and includes both the preceding schedules

The code to create these schedules (you need to do it only once)

begin 
 -- Schedule for 7th and 22nd of the month falling on Saturday
 dbms_scheduler.create_schedule(schedule_name  => 'sched_7_22_if_sat',
                               repeat_interval => 'FREQ=YEARLY;
                                                   BYDATE=0107-1d,0122-1d,
                                                          0207-1d,0222-1d,
                                                          0307-1d,0322-1d,
                                                          0407-1d,0422-1d,
                                                          0507-1d,0522-1d,
                                                          0607-1d,0622-1d,
                                                          0707-1d,0722-1d,
                                                          0807-1d,0822-1d,
                                                          0907-1d,0922-1d,
                                                          1007-1d,1022-1d,
                                                          1107-1d,1122-1d,
                                                          1207-1d,1222-1d;
                                                   BYDAY=FRI'
                              );

-- Schedule for 7th and 22nd of the month falling on Sunday
 dbms_scheduler.create_schedule(schedule_name  => 'sched_7_22_if_sun',
                               repeat_interval => 'FREQ=YEARLY;
                                                   BYDATE=0107+1d,0122+1d,
                                                          0207+1d,0222+1d,
                                                          0307+1d,0322+1d,
                                                          0407+1d,0422+1d,
                                                          0507+1d,0522+1d,
                                                          0607+1d,0622+1d,
                                                          0707+1d,0722+1d,
                                                          0807+1d,0822+1d,
                                                          0907+1d,0922+1d,
                                                          1007+1d,1022+1d,
                                                          1107+1d,1122+1d,
                                                          1207+1d,1222+1d;
                                                   BYDAY=MON'
                              ); 

 -- Schedule for all 7th and 22nd 
 dbms_scheduler.create_schedule(schedule_name => 'sched_7_22', repeat_interval => 'FREQ=DAILY;BYMONTHDAY=7,22;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=23;BYMINUTE=0;INCLUDE=sched_7_22_if_sat,sched_7_22_if_sun');

end;
/

Now you can use the "sched_7_22" schedule as the repeat interval argument for your job.

To test this schedule:

DECLARE
start_date       TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date    TIMESTAMP;
BEGIN
start_date := systimestamp;
return_date_after := start_date;
FOR i IN 1..20 LOOP
 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('sched_7_22', start_date, return_date_after, next_run_date);
 DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date, 'dd mon yyyy'));
return_date_after := next_run_date;
END LOOP;
END;
/
next_run_date: 07 sep 2022
next_run_date: 22 sep 2022
next_run_date: 07 oct 2022
next_run_date: 21 oct 2022
next_run_date: 07 nov 2022
next_run_date: 22 nov 2022
next_run_date: 07 dec 2022
next_run_date: 22 dec 2022
next_run_date: 06 jan 2023
next_run_date: 23 jan 2023
next_run_date: 07 feb 2023
next_run_date: 22 feb 2023
next_run_date: 07 mar 2023
next_run_date: 22 mar 2023
next_run_date: 07 apr 2023
next_run_date: 21 apr 2023
next_run_date: 08 may 2023
next_run_date: 22 may 2023
next_run_date: 07 jun 2023
next_run_date: 22 jun 2023

Davide P

Hi,

you probably have to manage it by application and schedule the job every day or over a series of days

GregV

Hi,

One possibilty to get this schedule is to create 3 named schedules:

• One named schedule called "sched_7_22_if_sat" that will find the 7th and 22nd days that are falling on Saturday and in which case you take the preceding day Friday (hence the -1d in each value of the BYDATE clause)
• One named schedule called "sched_7_22_if_sun" that will find the 7th and 22nd days that are falling on Sunday and in which case you take the following day Monday (hence the +1d in each value of the BYDATE clause)
• One named schedule called "sched_7_22" that will find the 7th and 22nd days that are falling on week days, and includes both the preceding schedules

The code to create these schedules (you need to do it only once)

begin 
 -- Schedule for 7th and 22nd of the month falling on Saturday
 dbms_scheduler.create_schedule(schedule_name  => 'sched_7_22_if_sat',
                               repeat_interval => 'FREQ=YEARLY;
                                                   BYDATE=0107-1d,0122-1d,
                                                          0207-1d,0222-1d,
                                                          0307-1d,0322-1d,
                                                          0407-1d,0422-1d,
                                                          0507-1d,0522-1d,
                                                          0607-1d,0622-1d,
                                                          0707-1d,0722-1d,
                                                          0807-1d,0822-1d,
                                                          0907-1d,0922-1d,
                                                          1007-1d,1022-1d,
                                                          1107-1d,1122-1d,
                                                          1207-1d,1222-1d;
                                                   BYDAY=FRI'
                              );

-- Schedule for 7th and 22nd of the month falling on Sunday
 dbms_scheduler.create_schedule(schedule_name  => 'sched_7_22_if_sun',
                               repeat_interval => 'FREQ=YEARLY;
                                                   BYDATE=0107+1d,0122+1d,
                                                          0207+1d,0222+1d,
                                                          0307+1d,0322+1d,
                                                          0407+1d,0422+1d,
                                                          0507+1d,0522+1d,
                                                          0607+1d,0622+1d,
                                                          0707+1d,0722+1d,
                                                          0807+1d,0822+1d,
                                                          0907+1d,0922+1d,
                                                          1007+1d,1022+1d,
                                                          1107+1d,1122+1d,
                                                          1207+1d,1222+1d;
                                                   BYDAY=MON'
                              ); 

 -- Schedule for all 7th and 22nd 
 dbms_scheduler.create_schedule(schedule_name => 'sched_7_22', repeat_interval => 'FREQ=DAILY;BYMONTHDAY=7,22;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=23;BYMINUTE=0;INCLUDE=sched_7_22_if_sat,sched_7_22_if_sun');

end;
/

Now you can use the "sched_7_22" schedule as the repeat interval argument for your job.

To test this schedule:

DECLARE
start_date       TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date    TIMESTAMP;
BEGIN
start_date := systimestamp;
return_date_after := start_date;
FOR i IN 1..20 LOOP
 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('sched_7_22', start_date, return_date_after, next_run_date);
 DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date, 'dd mon yyyy'));
return_date_after := next_run_date;
END LOOP;
END;
/
next_run_date: 07 sep 2022
next_run_date: 22 sep 2022
next_run_date: 07 oct 2022
next_run_date: 21 oct 2022
next_run_date: 07 nov 2022
next_run_date: 22 nov 2022
next_run_date: 07 dec 2022
next_run_date: 22 dec 2022
next_run_date: 06 jan 2023
next_run_date: 23 jan 2023
next_run_date: 07 feb 2023
next_run_date: 22 feb 2023
next_run_date: 07 mar 2023
next_run_date: 22 mar 2023
next_run_date: 07 apr 2023
next_run_date: 21 apr 2023
next_run_date: 08 may 2023
next_run_date: 22 may 2023
next_run_date: 07 jun 2023
next_run_date: 22 jun 2023

user13620045

@GregV Working like a charm. Thanks a lot! The combination of schedules is a new trick for me.😊