Oracle schedule on first day of month without holidays

Question

I need to create schedule for run jobs in oracle. It'll run at first workday of month, but excluding holidays. When first workday of month is holiday, than run should be next workday after holiday.

I managed with first workday of each month and exclude holidays, but I don't know how to set run on workday after holiday...

HOLIDAYS

DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name   => 'SCHDL_COM_HOLIDAYS',
  repeat_interval => 'FREQ=YEARLY; BYDATE=0101,0111;'
);

FIRST WORKDAY

DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name   => 'SCHDL_FIRST_WD',
  repeat_interval => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=SCHDL_COM_HOLIDAYS; BYSETPOS=1'
);

score 2 · Accepted Answer · answered Dec 28 '16 at 15:59

Please follow the below steps:

1) Create schedule for all the holidays.

BEGIN
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'NYD_FRI',
      repeat_interval   => 'FREQ=YEARLY;BYDATE=1231;BYDAY=FRI',
      comments          => 'Friday alternative for New Year''s Day');
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'NYD_MON',
      repeat_interval   => 'FREQ=YEARLY;BYDATE=0102;BYDAY=MON',
      comments          => 'Monday alternative for New Year''s Day');
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'NewYearsDay',
      repeat_interval   =>    'FREQ=YEARLY;BYDATE=0101;BYDAY=MON,TUE,WED,THU,FRI;'
                           || 'INCLUDE=NYD_FRI,NYD_MON',
      comments          => 'New Year''s Day');
  );
END;
/

2) Create a schedule which clubs all the schedule created above.

BEGIN
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'FederalHolidays',
      repeat_interval   =>  'NewYearsDay,MartinLutherKing,PresidentsDay,',
      comments          => 'Federal Holidays');
END;

3) Create a job, note the “EXCLUDE” and “BYSETPOS” option

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'Run_Next_Day',
      job_type          => 'PLSQL_BLOCK',
      job_action        => 'begin Run_Next_Day; end; ',
      repeat_interval   => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=7',
      enabled           => TRUE,
      comments          => 'Skipping Holidays');
END;
/

Thanks for response. Could You explain why we create job for it? — Gadziu, Dec 28 '16 at 16:12
I just gave a complete solution. If you already have a job you can include `exclude` clause in that. — XING, Dec 28 '16 at 17:27

score 1 · Answer 2 · answered Dec 29 '16 at 09:23

@XING thank You for Your help. I modified your solution exactly to my needs.

The answer exactly for my problem is

New Year's Day

BEGIN
DBMS_SCHEDULER.create_schedule (
  schedule_name     => 'SCHD_NYD_MON',
  repeat_interval   => 'FREQ=YEARLY;BYDATE=0101;BYDAY=MON',
  comments          => 'Monday alternative for New Year''s Day');
DBMS_SCHEDULER.create_schedule (
  schedule_name     => 'SCHD_NEW_YEAR_DAY',
  repeat_interval   =>    'FREQ=YEARLY;BYDATE=0101;BYDAY=MON,TUE,WED,THU,FRI;'
                       || 'INCLUDE=SCHD_NYD_MON',
  comments          => 'New Year''s Day');
END;
/

First November's Day

BEGIN
DBMS_SCHEDULER.create_schedule (
  schedule_name     => 'SCHD_FNOV_MON',
  repeat_interval   => 'FREQ=YEARLY;BYDATE=1101;BYDAY=MON',
  comments          => 'Monday alternative for First November');
DBMS_SCHEDULER.create_schedule (
  schedule_name     => 'SCHD_FIRST_NOVEMBER',
  repeat_interval   =>    'FREQ=YEARLY;BYDATE=1101;BYDAY=MON,TUE,WED,THU,FRI;'
                       || 'INCLUDE=SCHD_FNOV_MON',
  comments          => 'First November''s Day');
END;
/

Federal Holidays

BEGIN
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'SCHD_HOLIDAYS',
      repeat_interval   => 'SCHD_NEW_YEAR_DAY,SCHD_FIRST_NOVEMBER',
      comments          => 'Federal Holidays');
END;
/

Job

BEGIN
  DBMS_SCHEDULER.create_job (
     job_name          => 'Run_Next_Day',
     job_type          => 'PLSQL_BLOCK',
     job_action        => 'begin Run_Next_Day; end; ',
     repeat_interval   => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; 
                           byhour=12;byminute=0;bysecond=0; 
                           EXCLUDE=SCHD_HOLIDAYS; BYSETPOS=1',
     enabled           => TRUE,
     comments          => 'Skipping Holidays');
END;
/

Oracle schedule on first day of month without holidays

2 Answers2