Is there a way to express the following as
one Oracle calendaring expression?
Referenced:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12415 Mon-Thurs hourly except 5PM and 8PM, Fri 12AM-4PM(Currently I have two separate calendar strings:
FREQ=HOURLY;BYHOUR=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,21,22,23;BYMINUTE=11,26,41,56;BYDAY=MON,TUE,WED,THU;
FREQ=HOURLY;BYHOUR=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16;BYMINUTE=11,26,41,56;BYDAY=FRI;
)
Hoping to combine into one expression if possible. Thanks in advance!
A single "FREQ" I don't think is possible, but that doesn't mean that you don't have lots of options here
1) repeat function
A repeat interval can be a plsql function that returns the next time we should run, eg
SQL> create table t ( x date );
Table created.
SQL>
SQL> create or replace function my_special_interval return date is
2 begin
3 return sysdate + 30/86400;
4 end;
5 /
Function created.
SQL>
SQL> begin
2 dbms_scheduler.create_job (
3 job_name => 'MY_JOB',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'begin insert into t values (sysdate); commit; end;',
6 start_date => sysdate,
7 repeat_interval => 'my_special_interval',
8 enabled => true);
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select * from t;
X
-------------------
04/09/2020 14:15:39
SQL> /
X
-------------------
04/09/2020 14:15:39
04/09/2020 14:16:09
2) Combined schedules
You can create 2 schedules as per your above definition, and then create one that combines the two using the INCLUDE clause
3) Superset plus PLSQL
A common thing I see is people create a schedule which is the superset of both and then just alter the job text, ie
job_action=>q'{my_proc;}'
becomes
job_action=>q'{if to_char(sysdate,'D') = 1 and .... then my_proc; end if;}'
So they have the full flexibility of PLSQL to control exactly what conditions they'd like the job to run under./