CODEWITHSUNDEEP
oracle12cdbms-scheduler
Martin K.
Martin K.

Reputation: 1060

Oracle dbms_scheduler error with BYTIME

I was trying to get a job run every business day (MON to SAT) at 6:30am which the Oracle scheduler refused with

ORA-27419 "unable to determine valid execution date from repeat interval"

I started losing my mind when i discovered the following behaviour:

First, create a dummy job. Note that it has no schedule and is not enabled.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"TMP_DUMMY"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
                              dbms_lock.sleep(5);
                           end;',
            number_of_arguments => 0,
            start_date => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'Test Job');




    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"TMP_DUMMY"', attribute => 'store_output', value => TRUE);
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => '"TMP_DUMMY"',    attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

END;
/

Next step, set a repeat_interval using BYTIME with any execution time which is equal to or less than 02:55 (MI:SS) after the full hour. It does not matter whether this is done with or without the Hour part and for the former option the exact hour does not matter as well.

BEGIN
   DBMS_SCHEDULER.set_attribute( name => '"TMP_DUMMY"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYTIME=010255');
   DBMS_SCHEDULER.enable(name=>'"TMP_DUMMY"');
END; 
/

This works perfectly fine for me.

Now i want to increase the BYTIME by 1 second to 02:56 (MI:SS)

BEGIN
   DBMS_SCHEDULER.set_attribute( name => '"TMP_DUMMY"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYTIME=010256');
END; 
/

Running this attribute change i get

ORA-27470: failed to re-enable "[schema]"."TMP_DUMMY" after making requested change ORA-27419: unable to determined valid execution date from repeat interval

I have verified this behaviour for all MI:SS combinations:

set serveroutput on
DECLARE
  l_rep_interval VARCHAR2(50 CHAR);
BEGIN
FOR mi IN 0..59
LOOP
  FOR ss IN 0..59
  LOOP
    l_rep_interval := 'FREQ=DAILY;BYTIME='||lpad(to_char(mi*100+ss),4,'0');
    DBMS_SCHEDULER.set_attribute( name => '"TMP_DUMMY"', attribute => 'repeat_interval', value => l_rep_interval);
    DBMS_SCHEDULER.enable(name=>'"TMP_DUMMY"');
    DBMS_OUTPUT.PUT_LINE(l_rep_interval);
  END LOOP; --end ss
END LOOP; --end mi

EXCEPTION WHEN OTHERS THEN NULL;

END; 
/

It is working properly from 00:00 until 02:55 and fails for all other times. For me this looks like the MI:SS part is treated as a tinyint and higher values cause a type overflow.

Is this a Bug in the scheduler or am i missing something here? Oracle version is 12c.

Upvotes: 4

Views: 4005

Answers (1)

PaulW
PaulW

Reputation: 31

I just ran into this same issue. It looks like (from the documentation) that BYTIME is not recognised (even though SQLDeveloper uses it if you look at the SQL tab of the job GUI)

I found that the following works using BYMinute and ByHour

DBMS_SCHEDULER.set_attribute( name => '"TEST"."JOB"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=9;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN');

Hope this helps someone.

Upvotes: 3

Related Questions