Can not Run Completed Oracle Job again

Question

Can not run a completed DBMS_SCHEDULER job by remove the END_DATE

Hello, everyone! I am using oracle 12cR1,now I have a problem in DBMS_SCHEDULER jobs.

First, I created an repeated oracle DBMS_SCHEDULER jobs with END_DATE was set, after the set END_DATE, the job completed successfully, and the enabled state of job changed to disabled automatically. According to the running log of the job, the Operation was COMPLETED, while Additional info was REASON="End time reached" That was expected.

Then I wanted to run the job again, I removed the END_DATE field by

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE('JOB_XXX', 'END_DATE', '');

and set the job enable by

SYS.DBMS_SCHEDULER.ENABLE(name => 'JOB_XXX');

I can see the job was enabled again and END_DATE was empty. But The Job run again only once, and stopped, the running log of was COMPLETED, while Additional info was REASON="End time reached" again.

BEGIN
     sys.dbms_scheduler.CREATE_JOB(
     JOB_NAME            => 'JOB_3358',
     job_type            => 'STORED_PROCEDURE',
     JOB_ACTION          => 'TEST_JOB',
     START_DATE          => to_date('2019-05-05 13:35:00','yyyy-mm-dd hh24:mi:ss'),
     REPEAT_INTERVAL     => 'FREQ= SECONDLY;INTERVAL=30',
     END_DATE            => to_date('2019-05-05 13:38:00','yyyy-mm-dd hh24:mi:ss'),
     auto_drop           => FALSE,
     COMMENTS            => NULL);
    END;
/


begin
  sys.dbms_scheduler.enable(name => 'JOB_3358');
end;
/

What I expected was that the job will run according to REPEAT_INTERVAL again, and as end_date was empty, it should never stop.

Is there any mistake in removing END_DATE, or is this the oracle's bug?

Thanks in advance, and best Regards!

Onceover

Just guess: DBMS_SCHEDULER.set_attribute_null (name=>'JOB_3358', attribute=>'end_date') – Zephaniah

I tried, it didn't work, the job stopped.Thanks anyway! – Onceover

Maybe that link can help you rech your goal- – Yolandoyolane

Answer 1

tricky one. I reproduced your problem. Then, I tried to change start_date to systimestamp when removing end_date, which again did not work. But, then I changed start_date to systimestamp plus a bit when removing end_date, and then it worked. Working example below. It seems some info about the job is cached/stored somewhere and we can remove this info by setting start_date slightly into the future so that scheduling-logic is triggered when enabling the job (my wild theory on what happens). Working example for removing end_date of completed job:

BEGIN
     sys.dbms_scheduler.CREATE_JOB(
     JOB_NAME            => 'MYUSER.JOB_3358',
     job_type            => 'PLSQL_BLOCK',
     JOB_ACTION          => 'begin null; end;',
     START_DATE          => systimestamp,
     REPEAT_INTERVAL     => 'FREQ= SECONDLY;INTERVAL=30',
     END_DATE            => systimestamp + interval '2' minute,
     auto_drop           => FALSE,
     COMMENTS            => NULL);
    END;
/

begin
  sys.dbms_scheduler.enable(name => 'MYUSER.JOB_3358');
end;
/

-- wait until job shows as completed

exec DBMS_SCHEDULER.set_attribute_null (name=>'MYUSER.JOB_3358', attribute=>'end_date');

begin
          dbms_scheduler.set_attribute (
            name      => 'MYUSER.JOB_3358',
            attribute => 'start_date',
            value     => systimestamp + interval '1' minute);
    end;
    /

begin
  sys.dbms_scheduler.enable(name => 'MYUSER.JOB_3358');
end;
/

-- job will continue to run every 30 seconds indefinitely

--cleanup
exec sys.dbms_scheduler.drop_JOB(     JOB_NAME            => 'MYUSER.JOB_3358');

---

Edit: the above does NOT reliably work. It works sometimes, but not always. The only (silly!!!) approach which reliably worked so far in my tests is:

exec DBMS_SCHEDULER.set_attribute_null (name=>'MYUSER.JOB_3358', attribute=>'end_date');
-- This line raises "ORA-27469: NEXT_RUN_DATE is not a valid job attribute" but is necessary.
exec DBMS_SCHEDULER.set_attribute_null (name=>'MYUSER.JOB_3358', attribute=>'next_run_date');
exec dbms_scheduler.enable(name => 'MYUSER.JOB_3358');

Humus

Wow, great work. It's crazy that you have to run a command that raises an exception in order to make the job run. – Anticatalyst

Thank you, It seems the job is cached/stored, because the next day, I enabled the job again(when the end_date is null), it starts to run and won't stop any more. As the job was created via Windows Form(not using oracle client directly), when the Save button on form is clicked, I send back two commands to set the end_date, the first one is set the end_date to 2999/12/31, then the second one set the end_date to null, So the job will stop at 2999/12/31 – Onceover