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 :

declare
    v_cal_string varchar2(4000);
    v_next_date timestamp;
begin
    dbms_output.put_line('Now is : '||to_char(sysdate, 'DY DD-MON-YYYY HH24:MI'));
    -- Calendar string for every day at 06:15
    v_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…

declare
    v_cal_string varchar2(4000);
    v_next_date timestamp;
begin
    dbms_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

declare
    v_weekday_string varchar2(4000) := 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6; BYMINUTE=15';
    v_next_run timestamp;
    v_start_from timestamp;
begin
    dbms_output.put_line('Weekday...');
    -- I want to know that the calendar string will hit Monday - Friday but not Saturday or Sunday
    v_start_from := to_timestamp(next_day(sysdate, 'TUESDAY'));

    for i in 1..5 loop
       
        dbms_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 string
        v_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…

declare
    v_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;

begin
    dbms_output.put_line('Weekday...');
    v_start_date := to_timestamp(next_day(systimestamp, 'MONDAY'));
    for i in 1..2 loop
        
        dbms_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 iteration
        v_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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.