0

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;
GomuGomuZoro
  • 313
  • 1
  • 4
  • 16

1 Answers1

1

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;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • executing the SET_ATTRIBUTE part gives the following error: ORA-27412: repeat interval or calendar contains invalid identifier – GomuGomuZoro Feb 06 '18 at 14:24
  • Maybe you did a typo? – Wernfried Domscheit Feb 06 '18 at 14:48
  • No I copied yours and checked to make sure it is exactly the same. Is it expecting a different attribute than repeat_interval in order to combine the schedule? – GomuGomuZoro Feb 06 '18 at 21:43
  • 1
    Perhaps you copied this bit exactly: `BYMINUTE=0,1,2,3,4,5,[...],43,44` which is not valid syntax - you're expected to list out all the minutes. – Jeffrey Kemp Feb 07 '18 at 03:40
  • Nope :( I made sure to list out all the minutes in that – GomuGomuZoro Feb 07 '18 at 11:02
  • Please post your exact code. Which Oracle version do you have? – Wernfried Domscheit Feb 07 '18 at 11:12
  • 11g (11.12.0.3) 64 bit the code that is not working is the following: BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'MY_TEST_JOB', attribute => 'repeat_interval', value => 'JOB_PERIOD_1,JOB_PERIOD_2' ); END; the error is *Cause: The calendar string or calendar definition for the repeat interval of a job, schedule or window contained an unsupported keyword or reference to an undefined calendar. *Action: Correct the repeat interval such that it no longer contains the invalid keyword. – GomuGomuZoro Feb 09 '18 at 10:17
  • And what about the two others? – Wernfried Domscheit Feb 09 '18 at 10:23
  • I ran Select * from DBA_SCHEDULER_schedules; and I can see that both JOB_PERIOD_1 and JOB_PERIOD_2 are there. – GomuGomuZoro Feb 09 '18 at 12:22
  • And what are the repeat intervals of them? – Wernfried Domscheit Feb 09 '18 at 12:24
  • JOB_PERIOD_1: 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 JOB_PERIOD_2: FREQ=SECONDLY;INTERVAL=5;BYHOUR=19;BYMINUTE=0,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,41,42,43,44 – GomuGomuZoro Feb 09 '18 at 12:45
  • Do you have a newer Oracle release? Could be an Oracle bug. – Wernfried Domscheit Feb 09 '18 at 13:58
  • Apologies for the late reply. I figured out why I was getting the error. I executed the first part with a different user and later executed the "set_attribute" part with another. To avoid this issue I made sure to include the schema name. So instead of JOB_PERIOD_1, JOB_PERIOD_2, I edited them to .JOB_PERIOD_1,.JOB_PERIOD_1. Now all scripts will execute with the same schema name and they all executed successfully. I was also able to test and confirm the job is running as I wanted it to, so thanks for your help! – GomuGomuZoro Feb 19 '18 at 09:49