Forum Stats

  • 3,875,456 Users
  • 2,266,916 Discussions
  • 7,912,219 Comments

Discussions

How to accomplish this job using Oracle 19c schedule?

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.

Best Answer

  • GregV
    GregV Member Posts: 3,106 Gold Crown
    edited Aug 31, 2022 1:51PM Answer ✓

    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

Answers

  • Davide P
    Davide P Member Posts: 21 Green Ribbon

    Hi,

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

  • GregV
    GregV Member Posts: 3,106 Gold Crown
    edited Aug 31, 2022 1:51PM Answer ✓

    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
  • user13620045
    user13620045 Member Posts: 4 Blue Ribbon

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

    GregV