Use DBMS_SCHEDULER repeat_interval to run a task once in two days

Question

I am trying to create a task (delete some cache data) that will run once in two days. This will run on Oracle 11g. So far I came up with the following anonymous block:

 begin 
 DBMS_SCHEDULER.CREATE_JOB (
     job_name             => 'clear_cache',
     job_type             => 'PLSQL_BLOCK',
     job_action           => 'begin delete from MY_CACHE;commit; end;',
     start_date           =>  to_date('19/09/2016','dd/mm/rrrr')+ 19/24,
     repeat_interval      => 'to_date(''19/09/2016'',''dd/mm/rrrr'')+ 2 + 19/24',
     enabled              => TRUE); 
  end;

However, I am not sure about repeat_interval value..

Assuming that I will run this block today (15/09/2016), I want clear_cache to be executed on:

19/09/2016 at 7 p.m 
21/09/2016 at 7 p.m.
23/09/2016 at 7 p.m. 
etc  

I know that if i use

 start_date           =>  sysdate,
 repeat_interval      => 'trunc(sysdate) + 7 + 7/24'

Then it will start execution today, will repeat every 7 days at 7 p.m., what I want,though, is to begin next Monday and repeat every 2nd day and I am not sure how to achieve that...

So, I would like to know what exactly to put into repeat_interval ...

Thanks.

Answer 1

It's worth using the built-in calendaring syntax rather than trying to roll your own. By stating that you want the job to run daily with an interval of 2 it will run every 2 days.

The syntax is FREQ=DAILY;INTERVAL=2, if you set the start date to 7pm then it'll start at that time in your current timezone.

The start_date parameter is a date, so you can use an actual date or timestamp here.

DBMS_SCHEDULER.CREATE_JOB (
     job_name             => 'clear_cache',
     job_type             => 'PLSQL_BLOCK',
     job_action           => 'begin delete from MY_CACHE; commit; end;',
     start_date           => timestamp '2016-09-19 19:00:00',
     repeat_interval      => 'FREQ=DAILY;INTERVAL=2',
     enabled              => TRUE);