Thanks for the question, Naveed.
Asked: October 29, 2001 - 11:04 am UTC
Last updated: October 29, 2001 - 11:04 am UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
( New text added at end if 1st paragraph )
We are currently using the dbms_job package to schedule a job that should run every night Monday through Friday except on holidays. We can schedule it properly to run on weekdays, but how can we tell it to skip holidays ( for example not to run for Thanksgiving, Xmas and New Years ). We presently don't have any way to tell from the database if a certain date is a Holiday, but someone from our team said that we could setup a table, but we would have to maintain it anually to remove last years holidays, and add this years because they are know only after the first of the year.
I consulted Expert one-on-one Oracle( Appendix A ), and found that we can setup like below to skip Saturday and Sunday,
declare
l_job number;
begin
dbms_job.submit( job => l_job,
what => 'process_work_day_data;';
next_date => trunc( sysdate) + 1 + 1/24,
interval => 'trunc(sysdate + decode( to_char(sysdate, 'd' ), 6, 3, 1 ) + 1/24' );
end;
/
This would run 1:00 am M - F, but would not skip holidays. Do we need a scheduling package of some sort? Any recommendations?
Thanks alot.
and Tom said...
Yes, you would have to setup and maintain your own table of holidays -- some don't move (christmas), some do (thanksgiving)... Nothing I can do there.
Read on in the dbms_job chapter where I describe "custom scheduling". This is an exmaple of a next_date that is too hard to compute using a simple select from dual as dbms_job would do.
You will add a parameter p_next_date IN OUT date to your procedure process_work_day_data. It can then procedurally determine the next time it'll run. For exmaple it could be as easy as:
....
as
...
l_cnt number;
begin
p_next_date := trunc(sysdate);
loop
p_next_date := p_next_date+1;
if ( to_char(p_next_date,'Dy') not in ( 'Sat', 'Sun' ) )
then
select count(*) into l_cnt
from holidays
where dt_col = p_next_date;
if ( l_cnt = 0 )
then
EXIT; -- the loop, we found our day!!
end if;
end if;
end loop;
p_next_date := p_next_date+1/24;
......
That'll start p_next_date at sysdate+1. If that is NOT sat or sun AND we cannot find that day in the holidays table -- we exit out of the loop and add 1 hour. Else, we go and add 1 day and try again (and again) until we find a day that is not sat/sun and not in the holidays table.
Is this answer out of date? If it is, please let us know via a Comment