The REPEAT_INTERVAL of a Scheduler job is a calendar expression: a frequency, an interval, and BY clauses that pick months, days, hours, and minutes. DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING computes the run dates an expression produces, so you can test it before giving it to a job.
Code for This Guide
The main examples are in the examples/pkg-scheduler folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
FREQ=YEARLY | MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY
[; INTERVAL=n]
[; BYMONTH=...] [; BYMONTHDAY=...] [; BYDAY=...] [; BYDATE=MMDD,...]
[; BYHOUR=...] [; BYMINUTE=...] [; BYSETPOS=...]
dbms_scheduler.evaluate_calendar_string(calendar, start_date, return_date_after,
next_run_date_out);Test Common Expressions
SHOW prints the next three run dates of an expression, starting on Sunday 15 March 2026 at 09:00 in Dubai.
Example:
declare
v_start timestamp with time zone := timestamp '2026-03-15 09:00:00 Asia/Dubai';
procedure show (p_calendar varchar2) is
v_next timestamp with time zone := v_start;
begin
dbms_output.put_line(p_calendar);
for i in 1 .. 3 loop
dbms_scheduler.evaluate_calendar_string(p_calendar, v_start, v_next, v_next);
dbms_output.put_line(' ' || to_char(v_next, 'Dy DD-MON-YYYY HH24:MI'));
end loop;
end;
begin
show('FREQ=HOURLY; INTERVAL=4');
show('FREQ=WEEKLY; BYDAY=MON,FRI; BYHOUR=6; BYMINUTE=0');
show('FREQ=MONTHLY; BYMONTHDAY=-1; BYHOUR=23'); -- last day of the month
show('FREQ=MONTHLY; BYDAY=1SUN; BYHOUR=4'); -- first Sunday
show('FREQ=YEARLY; BYDATE=0101,0701; BYHOUR=0');
end;
/Output:
FREQ=HOURLY; INTERVAL=4 Sun 15-MAR-2026 13:00 Sun 15-MAR-2026 17:00 Sun 15-MAR-2026 21:00 FREQ=WEEKLY; BYDAY=MON,FRI; BYHOUR=6; BYMINUTE=0 Mon 16-MAR-2026 06:00 Fri 20-MAR-2026 06:00 Mon 23-MAR-2026 06:00 FREQ=MONTHLY; BYMONTHDAY=-1; BYHOUR=23 Tue 31-MAR-2026 23:00 Thu 30-APR-2026 23:00 Sun 31-MAY-2026 23:00 FREQ=MONTHLY; BYDAY=1SUN; BYHOUR=4 Sun 05-APR-2026 04:00 Sun 03-MAY-2026 04:00 Sun 07-JUN-2026 04:00 FREQ=YEARLY; BYDATE=0101,0701; BYHOUR=0 Wed 01-JUL-2026 00:00 Fri 01-JAN-2027 00:00 Thu 01-JUL-2027 00:00 PL/SQL procedure successfully completed.
- INTERVAL=4 with FREQ=HOURLY runs every four hours from the start time.
- BYDAY=MON,FRI picks two days a week, at the hour and minute given.
- BYMONTHDAY=-1 counts from the end: the last day of each month.
- BYDAY=1SUN is the first Sunday of each month, and BYDATE picks fixed dates.
Business Hours, Last Weekday, Fixed Days
Example:
declare
v_start timestamp with time zone := timestamp '2026-03-15 09:00:00 Asia/Dubai';
procedure show (p_calendar varchar2) is
v_next timestamp with time zone := v_start;
begin
dbms_output.put_line(p_calendar);
for i in 1 .. 3 loop
dbms_scheduler.evaluate_calendar_string(p_calendar, v_start, v_next, v_next);
dbms_output.put_line(' ' || to_char(v_next, 'Dy DD-MON-YYYY HH24:MI'));
end loop;
end;
begin
-- every 15 minutes, only from 9 to 17 on weekdays
show('FREQ=MINUTELY; INTERVAL=15; BYHOUR=9,10,11,12,13,14,15,16; '
|| 'BYDAY=MON,TUE,WED,THU,FRI');
-- the last weekday of each month
show('FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1; BYHOUR=18; BYMINUTE=0');
-- the 1st and 15th of each month
show('FREQ=MONTHLY; BYMONTHDAY=1,15; BYHOUR=7; BYMINUTE=30');
end;
/Output:
FREQ=MINUTELY; INTERVAL=15; BYHOUR=9,10,11,12,13,14,15,16; BYDAY=MON,TUE,WED,THU,FRI Mon 16-MAR-2026 09:00 Mon 16-MAR-2026 09:15 Mon 16-MAR-2026 09:30 FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1; BYHOUR=18; BYMINUTE=0 Tue 31-MAR-2026 18:00 Thu 30-APR-2026 18:00 Fri 29-MAY-2026 18:00 FREQ=MONTHLY; BYMONTHDAY=1,15; BYHOUR=7; BYMINUTE=30 Wed 01-APR-2026 07:30 Wed 15-APR-2026 07:30 Fri 01-MAY-2026 07:30 PL/SQL procedure successfully completed.
- Every 15 minutes, limited by BYHOUR and BYDAY to weekday office hours, so the first run is Monday at 09:00.
- BYSETPOS=-1 takes the last of the weekdays in each month: the last working day.
- BYMONTHDAY=1,15 runs twice a month.
Things to Know
- Times follow the time zone of the start date, so give it a region such as Asia/Dubai.
- Named schedules and EXCLUDE= clauses can combine calendars, for example to skip holidays.
- Always test a new expression with EVALUATE_CALENDAR_STRING before it goes into production.
Related Guides
Conclusion
Scheduler calendar expressions describe when a job repeats with FREQ, INTERVAL, and BY clauses. Test each one with EVALUATE_CALENDAR_STRING, use negative values and BYSETPOS for "last" rules, and anchor them in a time zone region.
See my articles more often when you search Google.
