oracle-databaseplsqlrecurrencerfc2445

Oracle PLSQL Recurrence Pattern RFC 2445


I have a requisite on which I need to convert a RFC 2445 Recurrence Pattern to Dates using PLSQL.

Example:

RRULE = FREQ=DAILY;INTERVAL=5;COUNT=10

From that rule, I need to write a table with the next 10 occurrences of that pattern. Something like the image bellow, considering start date as 1/1/2019 12:00:00 AM:

enter image description here

Does Oracle provides any PLSQL Package that allows me to do this? If doesn't, does anybody knows any PLSQL project initiative for this?

Ps: this is the same exactly pattern that Oracle uses on Job Schedules.


Solution

  • DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING might be able to do this.

    The syntax supported by the package seems similar to RFC 2445, but not identical. The below PL/SQL block prints out the dates based on a calendar string. There are some complications, such as parsing out the COUNT=10 to determine how many times to repeat the calculation.

    declare
        --Test different calendar strings and start dates.
        --p_calendar_string varchar2(4000) := 'FREQ=DAILY;INTERVAL=5;';
        p_calendar_string varchar2(4000) := 'FREQ=DAILY;INTERVAL=5;COUNT=10';
        p_start_date date := timestamp '2019-01-01 00:00:00';
    
        v_next_run_date date;
        v_count number;
        --Find the COUNT and remove it rom the calendar string, if it exists.
        procedure get_and_remove_count(p_calendar_string in out varchar2, p_count out number) is
        begin
            if lower(p_calendar_string) like '%count%' then
                p_count := to_number(regexp_substr(p_calendar_string, 'COUNT=([0-9]+)', 1, 1, null, 1));
                p_calendar_string := regexp_replace(p_calendar_string, 'COUNT=[0-9]+;?');
            else
                p_count := 1;
            end if;
        end;
    begin
        get_and_remove_count(p_calendar_string, v_count);
    
        --TEST
        --dbms_output.put_line('String: '||p_calendar_string||', count: '||v_count);
    
        --Start with the original date.
        v_next_run_date := p_start_date-1/24/60/60;
    
        --Loop through the COUNT and display all dates.
        for i in 1 .. v_count loop
    
            dbms_scheduler.evaluate_calendar_string
            (
                calendar_string   => p_calendar_string,
                start_date        => p_start_date,
                return_date_after => v_next_run_date,
                next_run_date     => v_next_run_date
            );
    
            dbms_output.put_line(to_char(v_next_run_date, 'mm/dd/yyyy hh:mi:ss am'));
        end loop;
    end;
    /
    

    Output:

    01/01/2019 12:00:00 am
    01/06/2019 12:00:00 am
    01/11/2019 12:00:00 am
    01/16/2019 12:00:00 am
    01/21/2019 12:00:00 am
    01/26/2019 12:00:00 am
    01/31/2019 12:00:00 am
    02/05/2019 12:00:00 am
    02/10/2019 12:00:00 am
    02/15/2019 12:00:00 am