Search code examples
oracleplsqloracle11gschedulerjobs

oracle dbms_scheduler repeat_interval


I have a procedure named MY_PROCEDURE_X in a packaged MY_PACKAGE_X. My requirement is that the procedure need to be executed on 1st and 16th of every month. If it is running on 1st of the Month, then the time of execution should be 10:00 AM, If it is running on 16th of the Month, then the time of execution should be 05:00 PM.

Can I make a single job to do this both? Below is my half done script:

BEGIN
 dbms_scheduler.create_job (
   job_name        => 'PROCESS_MY_JOB_X',
   JOB_TYPE        => 'PLSQL_BLOCK',
   JOB_ACTION      => 'MY_PACKAGE_X.MY_PROCEDURE_X',
   START_DATE      =>  TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI'),
   repeat_interval => 'FREQ=DAILY; INTERVAL=14', 
   ENABLED         =>  TRUE,
   comments        => 'RUN JOB ON 1ST AND 16TH OF EVERY MONTH');
END;
/

Thanks in advance ;)


Solution

  • EDIT: My previous answer did not actually work. BYHOUR could not be used in the named schedule for some reason - it gave an error.

    Instead I discovered a way to do it in a single repeat_interval expression:

    'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
    

    The trick here is that BYMONTHDAY=1,16 and BYHOUR=10,17 actually creates a set of four date/times:

    The 1st at 10, the 1st at 17, the 16th at 10, the 16th at 17

    Then BYSETPOS=1,4 picks the 1st and 4th date/times out of the set of four, and that is the two date/times we want.

    One can always test a repeat_interval expression using dbms_scheduler.evaluate_calendar_string like for example:

    declare
       startdate date;
       dateafter date;
       nextdate  date;
    begin
       startdate := TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI');
       dateafter := startdate;
       for i in 1..24 loop
          dbms_scheduler.evaluate_calendar_string(
             'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
           , startdate
           , dateafter
           , nextdate
          );
          dbms_output.put_line(to_char(nextdate,'YYYY-MM-DD HH24:MI'));
          dateafter := nextdate;
       end loop;
    end;
    /
    

    That block outputs this result:

    2014-11-16 17:00
    2014-12-01 10:00
    2014-12-16 17:00
    2015-01-01 10:00
    2015-01-16 17:00
    2015-02-01 10:00
    2015-02-16 17:00
    2015-03-01 10:00
    2015-03-16 17:00
    2015-04-01 10:00
    2015-04-16 17:00
    2015-05-01 10:00
    2015-05-16 17:00
    2015-06-01 10:00
    2015-06-16 17:00
    2015-07-01 10:00
    2015-07-16 17:00
    2015-08-01 10:00
    2015-08-16 17:00
    2015-09-01 10:00
    2015-09-16 17:00
    2015-10-01 10:00
    2015-10-16 17:00
    2015-11-01 10:00