I want to create a job that would run on 2am on the first Sunday in October
for every year, I tried with the below code. But got the error like,
Error report:
ORA-27419: unable to determine valid execution date from repeat interval
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
Here's the code for the create job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'To_DST_Australia',
job_type => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN
NULL;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=YEARLY; BYMONTH=OCT; BYDAY=1SUN; BYHOUR=2; BYMINUTE=00; BYSECOND=00',
end_date => NULL,
enabled => TRUE,
comments => '1st Sunday in October');
END;
/
Thanks in advance.
Use a MONTHLY frequency:
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := TO_TIMESTAMP_TZ('01-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS');
return_date_after := start_date;
FOR i IN 1..5
LOOP
dbms_scheduler.evaluate_calendar_string(
'FREQ=MONTHLY; BYMONTH=OCT; BYDAY=1SUN; BYHOUR=2',
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;
/
next_run_date: 06/OCT/13 02:00:00.000000 AM
next_run_date: 05/OCT/14 02:00:00.000000 AM
next_run_date: 04/OCT/15 02:00:00.000000 AM
next_run_date: 02/OCT/16 02:00:00.000000 AM
next_run_date: 01/OCT/17 02:00:00.000000 AM