oracle-databaseoracle11gdbms-schedulerdbms-job

Create scheduler job to run once in an year


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.


Solution

  • 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