Oracle dbms_scheduler error with BYTIME

I was trying to start a job every business day (MON to SAT) at 6:30 AM which Oracle's scheduler gave up with

ORA-27419 "unable to determine valid due date from retry Interval"

I started to lose my mind when I discovered the following behavior:

First create a dummy job. Please note that it has no timetable and is not included.

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 repeat_interval using BYTIME, with any execution time that is equal to or less than 02:55 (MI: SS) after a full hour. It doesn't matter if this is done with or without a clock element, and for the first option, the exact time doesn't matter either.

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 great for me.

Now I want to increase BYTIME by 1 second until 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: unable to re-enable "[schema]". 'TMP_DUMMY' after ORA-27419 change: unable to determine valid due date from retry interval

I have tested this behavior 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 works fine from 00:00 to 02:55 and does not work at all other times. To me it looks like the MI part: SS is treated as a tinyint value, and higher values ​​cause type overflow.

Is this a bug in the scheduler or am I missing something? Oracle version is 12c.

+3


source to share





All Articles