Oracle scheduler get all possible interval runs-CodePudding

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;"

"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

CodePudding user response：

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

CodePudding user response：

For an actual select, wrap dbms_scheduler.evaluate_calendar_string in a table function.

CREATE TYPE t_schedule_row AS OBJECT (
  id                 NUMBER,
  job_run_ts         TIMESTAMP
);
/

CREATE TYPE t_schedule_tab IS TABLE OF t_schedule_row;
/
  
CREATE OR REPLACE FUNCTION schedule_ts_tf 
(start_date_i IN TIMESTAMP
,end_date_i IN TIMESTAMP
,interval_i IN VARCHAR2
) RETURN t_schedule_tab AS
  l_tab  t_schedule_tab := t_schedule_tab();
  l_start_date        TIMESTAMP;
  l_return_date_after TIMESTAMP;
  l_next_run_date     TIMESTAMP;
  l_counter           NUMBER;
BEGIN
-- start date of schedule
  l_counter := 1;
  l_start_date := start_date_i;
  l_return_date_after := start_date_i;
  LOOP
    dbms_scheduler.evaluate_calendar_string(  
      interval_i,
      l_start_date, l_return_date_after, l_next_run_date);
  
    l_tab.extend;
    l_tab(l_tab.last) := t_schedule_row(l_counter, l_next_run_date);
    l_return_date_after := l_next_run_date;
    l_counter := l_counter   1;
    EXIT WHEN l_return_date_after > end_date_i;
  END LOOP;
  RETURN l_tab;
END;
/  

SELECT id, TO_CHAR(job_run_ts,'DD-MON-YYYY HH24:MI') 
FROM schedule_ts_tf
  (start_date_i => TIMESTAMP '2021-11-01 00:00:00',
   end_date_i => TIMESTAMP '2021-11-02 00:00:00',
   interval_i => 'FREQ=DAILY;BYHOUR=09,13;BYMINUTE=00;BYSECOND=0;'); 

        ID TO_CHAR(JOB_RUN_TS,'DD-MON
---------- --------------------------
         1 01-NOV-2021 09:00         
         2 01-NOV-2021 13:00         
         3 02-NOV-2021 09:00