Scheduler frequency and repeat intervals

When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING”


SQL>  set serveroutput on size 999999
SQL>
SQL> declare
  2    l_start_date TIMESTAMP;
  3    l_next_date TIMESTAMP;
  4    l_return_date TIMESTAMP;
  5  begin
  6    l_start_date := trunc(SYSTIMESTAMP);
  7    l_return_date := l_start_date;
  8
  9    for ctr in 1..10 loop
 10      dbms_scheduler.evaluate_calendar_string(
 11        'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10',
 12        l_start_date, l_return_date, l_next_date
 13        );
 14      dbms_output.put_line('Next Run on: ' ||
 15        to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
 16        );
 17      l_return_date := l_next_date;
 18    end loop;
 19  end;
 20  /
Next Run on: 03/31/2016 09:00:00
Next Run on: 03/31/2016 10:00:00
Next Run on: 04/01/2016 09:00:00
Next Run on: 04/01/2016 10:00:00
Next Run on: 04/04/2016 09:00:00
Next Run on: 04/04/2016 10:00:00
Next Run on: 04/05/2016 09:00:00
Next Run on: 04/05/2016 10:00:00
Next Run on: 04/06/2016 09:00:00
Next Run on: 04/06/2016 10:00:00

PL/SQL procedure successfully completed.