Oracle Scheduler – Creating a complex schedule with a single job

Question

I have a Job running on an Oracle 10g DB with a pretty simple execution plan.

BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=6,10,14,18

Now the problem is that we have to change the plan – but just for Mondays. So on Mondays the first job should run at 8 instead of 6. Then like all the others (10, 14, 18). Whereas from Tuesday to Friday it should run as above.

The easiest way would be to create a second job for the Monday and to remove the Monday from the original job. This, however, is a problem for our application, as it relies on just one job per import.

So the question is: Is there a way to tell the scheduler to run at 6,10,14,18 o'clock TUE-FRI and 8,10,14,18 on MON with a single job?

I read about specifying a repeat interval with a PL/SQL expression. But I didn't find out if there is way to do it.

Thanks for your help

Answer 1

create a composite schedule, and assign that to the job when you create it.

for example:

begin
  dbms_scheduler.create_schedule('MY_SCHED1', repeat_interval =>
  'FREQ=DAILY;BYDAY=TUE,WED,THU,FRI;BYHOUR=6,10,14,18');
END;
/
begin
dbms_scheduler.create_schedule('MY_SCHED2', repeat_interval =>
  'FREQ=DAILY;BYDAY=MON;BYHOUR=8,10,14,18');
END;
/
begin
dbms_scheduler.create_schedule('MAIN', repeat_interval =>
  'MY_SCHED1, MY_SCHED2');
END;
/

now you'd just assign the "MAIN" named schedule to the job instead of the schedule string.