Oracle dbms_scheduler for weekday execution Oracle Database Tips by Donald Burleson

For more working details on Oracle job scheduling and a complete code depot, see the wonderful $16.95 book Oracle Job Scheduling by Dr. Timothy Hall.  You can get the best deal (30%-off by) buying it directly from the publisher.

Also see Oracle dbms_scheduler examples and:

Oracle cron crontab examples

Windows Oracle Job Schedulers examples

It can be very tricky scheduling Oracle jobs that run only on weekdays.  Of course, it's much easier to use the 10g dbms_scheduler:

'freq=hourly; byday=MON,TUE,WED,THU,FRI; byminute=0; bysecond=0;'

Consider this example of a job to run hourly, but only on weekdays:

Jon Emmons also notes:

Hmm, interesting.  The erroneous result Mike got on this SQL is because he ran this on a day that is excluded.  This would only cause a problem at initial startup, and then only if the first run falls on  a weekend.

> SQL> select to_char(trunc(
>  2  least(
>  3  next_day(SYSDATE - 1,'MONDAY'),
>  4  next_day(SYSDATE - 1,'TUESDAY'),
>  5  next_day(SYSDATE - 1,'WEDNESDAY'),
>  6  next_day(SYSDATE - 1,'THURSDAY'),
>  7  next_day(SYSDATE - 1,'FRIDAY')
>  8  )
>  9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual
> SQL> /
>
> TO_CHAR(TRUNC(LEA
> -----------------
> 15-may-2006 12:00

If we can overlook that, I think we can manipulate the INTERVAL into
doing what we want by subtracting 23 hours instead of 24.  If we then
take the least of the NEXT_DAY, M-F after that and trunc it we get the
additional advantage of not having to bump it up the hour (since we
subtracted 23 then moved forward 1 (or more) days.

select to_char(trunc(
least(
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
23/24,'MONDAY'),
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
23/24,'TUESDAY'),
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
23/24,'WEDNESDAY'),
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
23/24,'THURSDAY'),
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
23/24,'FRIDAY')
),'HH'),'dd-mon-yyyy hh24:mi') from dual

22-may-2006 00:00


That at least keeps the example from running at midnight on Saturday.  I did not gather that the intention of this job was to start every Monday at 6:00am.  I thought it was just supposed to start at 6:00am tomorrow and continue at 1 hour intervals, M-F.

This is one of those examples which would be more easily scheduled with UNIX cron, but I think could be done here.  One (messy) way would be to have another job disable this job after 11:00pm on Friday and another enable it at 5:05am on Monday.  3 jobs to do the work of 1 isn't very pretty.

Let me know if this makes sense.  Making the job start at 6:00 am on Monday would probably take a fairly long INTERVAL definition and seems like a fairly unlikely scenario.

dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
  least
  (
    next_day(SYSDATE - 23/24,''MONDAY''),
    next_day(SYSDATE - 23/24,''TUESDAY''),
    next_day(SYSDATE - 23/24,''WEDNESDAY''),
    next_day(SYSDATE - 23/24,''THURSDAY''),
    next_day(SYSDATE - 23/24,''FRIDAY'')
  )
,''HH''
)',
TRUE,
:instno);
commit;
end;
/