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'
);

Answer 1

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;
/