CODEWITHSUNDEEP
sqloracle-databasedbms-scheduler
GomuGomuZoro
GomuGomuZoro

Reputation: 313

Modify DBMS_SCHEDULER job to stop running between certain hours and minutes

I need to alter a job to run between 23:00PM up till 19:45PM. I have tried the below which achieves this, but the problem with the below is that the job will always stop for 15minutes per hour, between for example 10:45 till 11:00 etc. I'm not sure if I can modify the below to solve this issue.

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (
    name => 'MY_TEST_JOB',
    attribute => 'repeat_interval',
    value => 'FREQ=SECONDLY;INTERVAL=5;
              BYHOUR=23,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19;
              BYDAY=mon,tue,wed,thu,fri,sat,sun;
              BYMINUTE=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40'

);

END;

Upvotes: 0

Views: 1943

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59466

Create two schedules and combine them:

exec DBMS_SCHEDULER.CREATE_SCHEDULE('JOB_PERIOD_1', 
   repeat_interval => 'FREQ=SECONDLY;INTERVAL=5;BYHOUR=23,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18');

exec DBMS_SCHEDULER.CREATE_SCHEDULE('JOB_PERIOD_2', 
   repeat_interval => 'FREQ=SECONDLY;INTERVAL=5;BYHOUR=19;BYMINUTE=0,1,2,3,4,5,[...],43,44');


BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE (
      name => 'MY_TEST_JOB',
      attribute => 'repeat_interval',
      value => 'JOB_PERIOD_1,JOB_PERIOD_2'
   );
END;

Actually I tested it only with this PL/SQL block.

DECLARE
   next_run_date TIMESTAMP := TIMESTAMP '2018-02-06 19:44:00';
   res INTEGER := 0;
BEGIN
    FOR i IN 1..30 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('JOB_PERIOD_1,JOB_PERIOD_2', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE(next_run_date);
    END LOOP;
END;

Upvotes: 1

Related Questions