Learning is not a spectator sport

Scheduler frequency and repeat intervals

April 1, 2016

1–2 minutes

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.

4 responses to “Scheduler frequency and repeat intervals”

fouedgray

April 2, 2016 at 2:12 am

Nice hint , thank you
Foued

Reply
Kim Berg Hansen

April 5, 2016 at 10:19 pm

Can’t resist the chance of a Shameless Plug(TM) 😉

http://www.kibeha.dk/2014/12/date-row-generator-with-dbmsscheduler.html

Pretty much the same, just wrapped in a table function to allow you to use the generated dates from the scheduler syntax rather than just outputting to screen 😉

Reply
1. Nimish Garg
  
  April 27, 2017 at 2:56 pm
  
  And an add-on: dbms_scheduler.create_schedule: Schedule Job to run at 13:15, 14:30, 15:45 in #Oracle Database: http://nimishgarg.blogspot.in/2016/04/dbmsscheduler-repeatinterval-tip-with.html
  
  Reply
Long running scheduler jobs – Learning is not a spectator sport

March 26, 2019 at 5:59 pm

[…] One of the nice things about the job scheduler in the Oracle database is the easily interpreted interval settings you can apply for job frequency. The days of cryptic strings like “sysdate+0.000694444” when all you really wanted to say was “Just run this job every minute” are a thing of the past. I covered how to get the database to convert interval string into real execution dates here https://connor-mcdonald.com/2016/04/01/understanding-scheduler-syntax/ […]

Reply