Create scheduler job to run once in an year

Question

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.

Changing the `FREQ=MONTHLY` instead of `YEARLY` executed fine. Still, unsure about reason for failure in case of `YEARLY`. If we use `YEARLY` specifying `BYDAY=SUN` works fine. — bprasanna, Oct 30 '13 at 08:24
Oracle docs on BYDAY: *"Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency."* http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sched.htm#ARPLS72263 — Jeffrey Kemp, Oct 30 '13 at 12:46
The 1st Sunday of the year doesn't occur in October, so the YEARLY expression doesn't match any date (since you stipulate that it should try to find the 1st Sunday in the year, but it has to be in October). — Jeffrey Kemp, Oct 30 '13 at 12:46

score 2 · Accepted Answer · answered Oct 30 '13 at 07:44

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

Create scheduler job to run once in an year

1 Answers1