Search code examples
oracle-databaseoracle-apexoracle19c

Oracle scheduler get all possible interval runs

I'm trying to get all the possible runs for all the scheduler I have on a database.

I'm trying to do a query/collection with the translation of repeat_interval(dba_scheduler_jobs) to multiple rows, for the current month.

eg:

job_name:"banana"
repeat_interval:"FREQ=DAILY;BYHOUR=09,13;BYMINUTE=00;BYSECOND=0;"
job_name:"potatoes"
repeat_interval:"FREQ=DAILY;BYHOUR=10;BYMINUTE=10,20,30;BYSECOND=0;"
job_name:"carots"
repeat_interval:"FREQ=DAILY;BYTIME=001500;"

To

"banana"   ; "01-11-2021 09:00:00"
"potatoes" ; "01-11-2021 10:10:00"
"potatoes" ; "01-11-2021 10:20:00"
"potatoes" ; "01-11-2021 10:30:00"
"banana"   ; "01-11-2021 13:00:00"
"carots"   ; "01-11-2021 15:00:00"
"banana"   ; "02-11-2021 09:00:00"
"potatoes" ; "02-11-2021 10:10:00"
"potatoes" ; "02-11-2021 10:20:00"
"potatoes" ; "02-11-2021 10:30:00"
"banana"   ; "02-11-2021 13:00:00"
"carots"   ; "02-11-2021 15:00:00"
"banana"   ; "03-11-2021 09:00:00"
"potatoes" ; "03-11-2021 10:10:00"
"potatoes" ; "03-11-2021 10:20:00"
"potatoes" ; "03-11-2021 10:30:00"
"banana"   ; "03-11-2021 13:00:00"
"carots"   ; "03-11-2021 15:00:00"
"banana"   ; "04-11-2021 09:00:00"
"potatoes" ; "04-11-2021 10:10:00"
"potatoes" ; "04-11-2021 10:20:00"
"potatoes" ; "04-11-2021 10:30:00"
"banana"   ; "04-11-2021 13:00:00"
"carots"   ; "04-11-2021 15:00:00"
...
"banana"   ; "30-11-2021 09:00:00"
"potatoes" ; "30-11-2021 10:10:00"
"potatoes" ; "30-11-2021 10:20:00"
"potatoes" ; "30-11-2021 10:30:00"
"banana"   ; "30-11-2021 13:00:00"
"carots"   ; "30-11-2021 15:00:00"

I already found the procedure dbms_scheduler.evaluate_calendar_string that gives me the date, but I'll need to do some sort of loop by hours->minutes->seconds I think?

I someone could help me with this, I'll be grateful.

Kind Regards, Davide

Solution

  • Where is your problem? Should simply work:

    DECLARE
    start_time TIMESTAMP := TIMESTAMP '2021-11-01 00:00:00'; 
    end_time TIMESTAMP := TIMESTAMP '2021-12-01 00:00:00';
    next_run_date TIMESTAMP := start_time;
BEGIN
    LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;BYHOUR=09,13;BYMINUTE=00;BYSECOND=0;', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE('banana => '|| TO_CHAR(next_run_date, 'dd-mm-yyyy hh24:mi:ss') );
        EXIT WHEN next_run_date >= end_time;
    END LOOP;
END;

banana => 01-11-2021 09:00:00
banana => 01-11-2021 13:00:00
banana => 02-11-2021 09:00:00
banana => 02-11-2021 13:00:00
banana => 03-11-2021 09:00:00
banana => 03-11-2021 13:00:00
banana => 04-11-2021 09:00:00
...
banana => 28-11-2021 09:00:00
banana => 28-11-2021 13:00:00
banana => 29-11-2021 09:00:00
banana => 29-11-2021 13:00:00
banana => 30-11-2021 09:00:00
banana => 30-11-2021 13:00:00
banana => 01-12-2021 09:00:00