oracle dbms_scheduler repeat_interval

Question

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 ;)

Meatiness

Answer 1

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

Icosahedron

Great.. Thanks for the evaluate_calendar_string examples. Let me check all these:) – Meatiness

evaluate_calendar_string is very useful – Teplica

Answer 2

perhaps a more intuitive way is to create two named schedules (say s1 and s2). The repeat_interval can then just the list of schedules "s1,s2" set echo on

begin
  dbms_scheduler.drop_schedule( schedule_name => 's1');
  dbms_scheduler.drop_schedule( schedule_name => 's2');
end;
/
begin
  dbms_scheduler.create_schedule( schedule_name => 's1',
       repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1');
  dbms_scheduler.create_schedule( schedule_name => 's2',
       repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=16;BYHOUR=17');
end;
/
set serveroutput on
begin
  print_dates('s1,s2', to_timestamp_tz('01-JAN-2022 20:00:00','DD-MON-YYYY HH24:MI:SS'), 10);
end;
/

A function to test schedules (repeat_interval)

 create or replace procedure print_dates 
 (
  cal_string in varchar2,
  start_date in timestamp with local time zone,
  nr_of_dates in pls_integer
 )
is
  date_after timestamp with local time zone := start_date - interval '1' second;
  next_execution_date timestamp with local time zone;
begin
  for i in 1 .. nr_of_dates
  loop
    dbms_scheduler.evaluate_calendar_string
     (cal_string, start_date, date_after, next_execution_date);

    dbms_output.put_line(to_char(next_execution_date,
                                 'DY DD-MON-YYYY (DDD-IW) HH24:MI:SS'));

    date_after := next_execution_date;
  end loop;
end;
/

Test result:

 -->
 SUN 16-JAN-2022 (016-02) 17:00:00  +00 +00:00
 TUE 01-FEB-2022 (032-05) 01:00:00  +00 +00:00
 WED 16-FEB-2022 (047-07) 17:00:00  +00 +00:00
 TUE 01-MAR-2022 (060-09) 01:00:00  +00 +00:00
 WED 16-MAR-2022 (075-11) 17:00:00  +00 +00:00
 FRI 01-APR-2022 (091-13) 01:00:00  +00 +00:00
 SAT 16-APR-2022 (106-15) 17:00:00  +00 +00:00
 SUN 01-MAY-2022 (121-17) 01:00:00  +00 +00:00
 MON 16-MAY-2022 (136-20) 17:00:00  +00 +00:00
 WED 01-JUN-2022 (152-22) 01:00:00  +00 +00:00
 
 PL/SQL procedure successfully completed.
 

Gymkhana

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Hallucinate