We got an requirement to schedule a script after BD6+1. Means next day to BD6, which can be a holiday. But incase between BD1 to BD6 federal holiday falls, it needs to be moved to next day.
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'); dbms_scheduler.create_schedule( schedule_name => 'MartinLutherKing', repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3MON', comments => 'Martin Luther King Day'); dbms_scheduler.create_schedule( schedule_name => 'PresidentsDay', repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3MON', comments => 'President''s Day'); dbms_scheduler.create_schedule( schedule_name => 'MemorialDay', repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1MON', comments => 'Memorial Day'); dbms_scheduler.create_schedule( schedule_name => 'ID_FRI', repeat_interval => 'FREQ=YEARLY;BYDATE=0703;BYDAY=FRI', comments => 'Friday alternative for Independence Day'); dbms_scheduler.create_schedule( schedule_name => 'ID_MON', repeat_interval => 'FREQ=YEARLY;BYDATE=0705;BYDAY=MON', comments => 'Monday alternative for Independence Day'); dbms_scheduler.create_schedule( schedule_name => 'IndependenceDay', repeat_interval => 'FREQ=YEARLY;BYDATE=0704;BYDAY=MON,TUE,WED,THU,FRI;' || 'INCLUDE=ID_FRI,ID_MON', comments => 'Independence Day'); dbms_scheduler.create_schedule( schedule_name => 'LaborDay', repeat_interval => 'FREQ=MONTHLY;BYMONTH=SEP;BYDAY=1MON', comments => 'Labor Day'); dbms_scheduler.create_schedule( schedule_name => 'ColumbusDay', repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2MON', comments => 'Columbus Day'); dbms_scheduler.create_schedule( schedule_name => 'VD_FRI', repeat_interval => 'FREQ=YEARLY;BYDATE=1110;BYDAY=FRI', comments => 'Friday alternative for Veterans Day'); dbms_scheduler.create_schedule( schedule_name => 'VD_MON', repeat_interval => 'FREQ=YEARLY;BYDATE=1112;BYDAY=MON', comments => 'Monday alternative for Veterans Day'); dbms_scheduler.create_schedule( schedule_name => 'VeteransDay', repeat_interval => 'FREQ=YEARLY;BYDATE=1111;BYDAY=MON,TUE,WED,THU,FRI;' || 'INCLUDE=VD_FRI,VD_MON', comments => 'Veterans Day'); dbms_scheduler.create_schedule( schedule_name => 'Thanksgiving', repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4THU', comments => 'Thanksgiving'); dbms_scheduler.create_schedule( schedule_name => 'XMS_FRI', repeat_interval => 'FREQ=YEARLY;BYDATE=1224;BYDAY=FRI', comments => 'Friday alternative for Christmas'); dbms_scheduler.create_schedule( schedule_name => 'XMS_MON', repeat_interval => 'FREQ=YEARLY;BYDATE=1226;BYDAY=MON', comments => 'Monday alternative for Christmas'); dbms_scheduler.create_schedule( schedule_name => 'Christmas', repeat_interval => 'FREQ=YEARLY;BYDATE=1225;BYDAY=MON,TUE,WED,THU,FRI;' || 'INCLUDE=XMS_FRI,XMS_MON', comments => 'Christmas'); end; /
– create a schedule which clubs all the schedule created above.
begin dbms_scheduler.create_schedule( schedule_name => 'FederalHolidays', repeat_interval => 'NewYearsDay,MartinLutherKing,PresidentsDay,' || 'MemorialDay,IndependenceDay,LaborDay,ColumbusDay,VeteransDay,' || 'Thanksgiving,Christmas', comments => 'Federal Holidays'); end; /
– Job which needs to be scheduled
create or replace procedure stats_run_after_BD6 as begin ---- some job; end; /
— Create a job, note the “EXCLUDE” and “BYSETPOS” option
BEGIN dbms_scheduler.create_job( job_name => 'Stats_Run_After_BD6+1' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin stats_run_after_BD6; end; ' ,repeat_interval => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=7' ,enabled => TRUE ,comments => 'Run Stats Gather next day of BD6'); END; /
What are all the dates this job is going to execute. We can use the below procedure.
create or replace procedure print_dates
is
nr_of_dates pls_integer :=30;
start_date timestamp with time zone := sysdate;
date_after timestamp with time zone := start_date - interval '1' second;
next_execution_date timestamp with time zone;
correct_execution_date timestamp with time zone;
begin
for i in 1 .. nr_of_dates
loop
dbms_scheduler.evaluate_calendar_string
('FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=6', start_date, date_after, next_execution_date);
correct_execution_date := next_execution_date + interval '1' day;
DBMS_OUTPUT.PUT_LINE(correct_execution_date);
date_after := next_execution_date;
end loop;
end;
/
sometime the BD6+ 1 date will be weekend. If you want to execute in the weekend you can add “interval ’1′ day”,
correct_execution_date := next_execution_date + interval ’1′ day;
else remove that.
convert this post to pdf.