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.
source to share
No one has answered this question yet
Check out similar questions: