Predicting the future with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
source link: https://mikesmithers.wordpress.com/2021/11/19/predicting-the-future-with-dbms_scheduler-evaluate_calendar_string/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Predicting the future with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
“Tempus Fugit !”…may well be similar to the collection of syllables that spring to mind when you find out the “fun” way that your carefully crafted DBMS_SCHEDULER calendar string doesn’t quite do what you thought it did.
Fortunately, the gift of clairvoyance is within your grasp if only you follow the Wisdom of the Ancient DBAs and their mantra of Legere mandata (Lit. “Read the instructions”)…
The path of enlightenment will eventually lead to to the mystical and and ancient ( well, at least since 11g) writings of :
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
… which reveals how to acquire the power to fortell the next date and time match for a given calendar string.
Let’s start with a simple example, a calender for the same time every day :
declarev_cal_string varchar2(4000);v_next_date timestamp;begindbms_output.put_line('Now is : '||to_char(sysdate, 'DY DD-MON-YYYY HH24:MI'));-- Calendar string for every day at 06:15v_cal_string := 'FREQ=DAILY; BYHOUR=6; BYMINUTE=15';dbms_scheduler.evaluate_calendar_string( calendar_string => v_cal_string,start_date => null,return_date_after => null,next_run_date => v_next_date);dbms_output.put_line('Next Run Date is : '||to_char(v_next_date, 'DY DD-MON-YYYY HH24:MI')); end;/Run this and we can see that, the next run date is tomorrow morning as expected ( unless you’ve started work especially early today) :
No chickens were sacrificed in the making of this screenshot
Note that all of the parameters to EVALUATE_CALENDAR_STRING are mandatory.
If you want something a bit more exciting, how about we pretend that payday is the third Thursday of the month…
declarev_cal_string varchar2(4000);v_next_date timestamp;begindbms_output.put_line('Now is : '||to_char(sysdate, 'DY DD-MON-YYYY HH24:MI'));v_cal_string := 'FREQ=MONTHLY; BYDAY=3 THU; BYHOUR=0; BYMINUTE=0';dbms_scheduler.evaluate_calendar_string( calendar_string => v_cal_string,start_date => null,return_date_after => null,next_run_date => v_next_date);dbms_output.put_line('Next Payday is : '||to_char(v_next_date, 'DY DD-MON-YYYY HH24:MI')); end;/That’s simple enough, but what if we want to check for something a bit more subtle, such as a string only for weekdays, or weekends for that matter.
If fiddling around with the FIXED_DATE init.ora parameter in your development environment doesn’t appeal, you have the rather simpler option of setting a value for RETURN_DATE_AFTER…
declarev_weekday_string varchar2(4000) := 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6; BYMINUTE=15';v_next_run timestamp;v_start_from timestamp;begindbms_output.put_line('Weekday...');-- I want to know that the calendar string will hit Monday - Friday but not Saturday or Sundayv_start_from := to_timestamp(next_day(sysdate, 'TUESDAY'));for i in 1..5 loopdbms_scheduler.evaluate_calendar_string(calendar_string => v_weekday_string,start_date => null,return_date_after => v_start_from,next_run_date => v_next_run);dbms_output.put_line(to_char( v_next_run, 'Day DDth Month YYYY HH24:MI'));-- set RETURN_DATE_AFTER to be the date last found by the stringv_start_from := v_next_run;end loop; end;/Alternatively, we can specify the start_date parameter. This time, we’ll test a calendar string for weekends…
declarev_weekend_string varchar2(4000) := 'FREQ=DAILY; BYDAY=SAT,SUN; BYHOUR=9; BYMINUTE=30'; -- I like a lie-in at weekends v_start_date timestamp;v_next_run timestamp;begindbms_output.put_line('Weekday...');v_start_date := to_timestamp(next_day(systimestamp, 'MONDAY'));for i in 1..2 loopdbms_scheduler.evaluate_calendar_string(calendar_string => v_weekend_string,start_date => v_start_date,return_date_after => null,next_run_date => v_next_run);dbms_output.put_line(to_char( v_next_run, 'Day DDth Month YYYY HH24:MI'));-- Unlike the RETURN_DATE_AFTER parameter, we need to increment the start date by a day-- in each loop iterationv_start_date := v_next_run + 1;end loop; end;/Whatever your future may hold, you can now be confident that it does not include further scheduling surprises.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK