Solved

Schedule a job every hour on certain days

  • 21 October 2021
  • 2 replies
  • 164 views

Userlevel 7
Badge +18

I received this question in a private message:

 

"I need to schedule a migration job every TUESDAY, WEDNESDAY, THURSDAY, FRIDAY every HOUR.

 

Could you please help me out with this requirement.”

icon

Best answer by durette 21 October 2021, 17:20

View original

This topic has been closed for comments

2 replies

Badge +1

Hi Durette, 

FREQ=HOURLY; BYDAY=TUE,WED,THU,FRI; BYMINUTE=0; BYSECOND=0; 

This query is not working for me , its throwing the above error.

Userlevel 7
Badge +18

You want to use a calendaring expression.

https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235

I believe this will give you what you want:

FREQ=HOURLY; BYDAY=TUE,WED,THU,FRI; BYMINUTE=0; BYSECOND=0;

Using SQL*Plus, here’s how to check to see what a particular schedule expression will do. This code shows the next 50 occurrences of a schedule string starting from right now.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
calendaring_expression_ VARCHAR2(32767) :=
'FREQ=HOURLY; BYDAY=TUE,WED,THU,FRI; BYMINUTE=0; BYSECOND=0;';
BEGIN
start_date := SYSTIMESTAMP;
return_date_after := start_date;
FOR i IN 1..50 LOOP
dbms_scheduler.evaluate_calendar_string(
calendaring_expression_,
start_date,
return_date_after,
next_run_date);
dbms_output.put_line('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
/