Some DBAs complain that Oracle's pre-10g job queue interface is lacking. Unlike cron or Windows Scheduler, pre-10g Oracle doesn't provide a mechanism to schedule jobs to run twice a week, on the first of the month, etc.
The truth is that Oracle's job queue interface has far greater flexibility than even the most full-featured o/s job scheduler. Jobs are scheduled using the sys.dbms_job.submit routine:
declare my_job number; begin dbms_job.submit(job => my_job, what => 'my_procedure(foo);' next_date => sysdate+1, interval => 'sysdate+1'); end; /
The arguments for dbms_job.submit include two time-related parameters, next_date and interval.
to_date('12/13/2004 02:34 PM','MM/DD/YYYY HH:MI AM');However, it's often quicker and more convenient to pass it in as a function of the SYSDATE function. Examples:
sysdate+1 --This time tomorrow trunc(sysdate)+1 --12:00 am tomorrow trunc(sysdate)+17/24 --5 pm (17:00) today(Recall that in Oracle date arithmetic, "+1" means add one day. "trunc(date)" returns midnight of the date passed in. Thus, "+17/24" means add 17/24ths of a day, so "trunc(sysdate)+17/24" means "17 hours past midnight today".)
The date passed in must, obviously, be greater than or equal to sysdate.
'sysdate+1' --Exactly 24 hours after the job's current run starts 'trunc(sysdate)+1' --Midnight after the day the job is run 'trunc(sysdate)+17/24' --5 PM on the day the job is runEach time the job is run, the varchar2 string you passed in as interval is reevaluated and the job's next run date is set to the result. (If the varchar2 string passed as interval does not evaluate to a date greater than today's, or to null, dbms_job.submit returns an error. If it evaluates to null, the job is simply not run again.)
The power here is that you can write your own functions and set them to return whatever date you like. Most powerfully, your functions can depend on database state. You could set a job to run every two weeks when the status of a certain employee in your tables was "on leave", and every month otherwise.
More prosaically, you can write your own functions to run jobs only when you need them to run. For example, if I wanted to run a job only Wednesdays at 3 pm and Fridays at 5 pm, I could write this quick function:
function date_to_run_emp_job return date is mydate date; begin if to_char(sysdate,'D') < 4 --Wednesday is the 4th day of the week in Oracle then mydate := trunc(sysdate,'W')+2+15/24 ; --Monday is the 1st day of week elsif to_char(sysdate,'D')=4 and sysdate < trunc(sysdate)+15/24 --ie. it's Wednesday but it's before 3 pm mydate := trunc(sysdate,'W')+2+15/24 ; else mydate := trunc(sysdate,'W')+4+17/24 ; --Friday at 5 pm end if; return mydate; end; /
The solution is a generic date package, owned by system, with execute rights granted to public, which will return some commonly wanted next_dates. Remember that the goal is to have a function that, when run right before each time job is run, evaluates to the next date you want the job to run. These functions are to be used for the interval argument of job_next_dates.
The package job_next_dates includes the following functions:
You must supply 24-hour times with a leading zero where appropriate, eg. 03:00 instead of 3:00, 00:24 instead of 0:24.
------------------------ CUT HERE ---------------------------- create or replace package job_next_dates is /* Author : NROSHAK Created : 12/17/2003 5:32:14 PM Purpose : Functions that return the next date specified. For the "interval" parameter of DBMS_JOB, which accepts a varchar2 string that evaluates to a date. These functions allow scheduling of database jobs on complex schedules. Example : dbms_job.submit(:job, 'myproc;', sysdate, 'job_next_dates.weekly(''MoTuWe'',''14:45'')' ); COPYRIGHT 2003 NATALKA ROSHAK */ -- In all of these functions, HOUR24 should be entered in the format -- HH:MI where HH is the 24-hour hour. -- eg. 14:45 -- 00:00 is a valid value, but 24:00 is not. function weekly (Sun in boolean, Mon in boolean, Tue in boolean, Wed in boolean, Thu in boolean, Fri in boolean, Sat in boolean, hour24 in varchar2) return date; function weekly (daystring in varchar2, hour24 in varchar2) return date; function friendly_date(date_string in varchar2, format_string in varchar2) return date; function specific_dates(format_string in varchar2, date1 in varchar2, date2 in varchar2 default null, date3 in varchar2 default null, date4 in varchar2 default null, date5 in varchar2 default null) return date; function specific_dates(format_string in varchar2, date1 in varchar2, date2 in varchar2 default null, date3 in varchar2 default null, date4 in varchar2 default null, date5 in varchar2 default null, hour24 in varchar2) return date; --use this function as "interval" argument to run a job every day at --the same time function every_day(hour24 in varchar2) return date; end job_next_dates; / create or replace package body job_next_dates is -- Private type declarations type days_t is table of boolean index by binary_integer; procedure debug (message in varchar2) is begin dbms_output.put_line ( message); end debug; function time_in_minutes (hchar in varchar2) return number --Accepts a 24-hour string like 09:45 --Returns number of minutes past midnight is hno number; hrs number; mins number; begin if hchar not like '__:__' then return -1; else hno := to_number(to_char(to_date(hchar,'hh24:mi'),'sssss'))/60 ; end if; return hno; exception when others then debug('Error in job_next_dates: Bad time supplied: ' || hchar); debug('Time must be in format HH24:MI'); return -1; end time_in_minutes; function weekly (Sun in boolean, Mon in boolean, Tue in boolean, Wed in boolean, Thu in boolean, Fri in boolean, Sat in boolean, hour24 in varchar2) return date is hno number; --number of minutes past midnight today_is varchar2(5); day_table days_t; next_day_to_run number := null; boost_index number; bad_time exception; no_day_supplied exception; begin --check format of hour hno := time_in_minutes(hour24); if hno < 0 then raise bad_time; end if; select to_char(sysdate,'D') into today_is --day number from dual; debug(today_is); day_table.delete; day_table(1) := Sun; day_table(2) := Mon; day_table(3) := Tue; day_table(4) := Wed; day_table(5) := Thu; day_table(6) := Fri; day_table(7) := Sat; day_table(8) := Sun; day_table(9) := Mon; day_table(10) := Tue; day_table(11) := Wed; day_table(12) := Thu; day_table(13) := Fri; day_table(14) := Sat; if hno/1440+trunc(sysdate) > sysdate then boost_index := 0; else boost_index := 1; end if; for i in today_is+boost_index..today_is+boost_index+6 loop if day_table(i) = true then next_day_to_run := i; exit; end if; end loop; if next_day_to_run is null then raise no_day_supplied; end if; return trunc(sysdate)-today_is+next_day_to_run + hno/1440 ; exception when bad_time then debug('Error in job_next_dates.weekly'); raise; when no_day_supplied then debug('Error in job_next_dates.weekly: No day of week supplied'); raise; when others then debug('Error in job_next_dates.weekly'); raise; end weekly; function weekly (daystring in varchar2, hour24 in varchar2) return date is --daystring: Contains any of MoTuWeThFrSaSu --eg. job_next_dates.weekly('MoTuWeSa','16:34'); mon boolean := false; tue boolean := false; wed boolean := false; thu boolean := false; fri boolean := false; sat boolean := false; sun boolean := false; begin if UPPER(daystring) like '%MO%' then mon := true; end if; if UPPER(daystring) like '%TU%' then TUE := true; end if; if UPPER(daystring) like '%WE%' then WED := true; end if; if UPPER(daystring) like '%TH%' then THU := true; end if; if UPPER(daystring) like '%FR%' then FRI := true; end if; if UPPER(daystring) like '%SA%' then SAT := true; end if; if UPPER(daystring) like '%SU%' then SUN := true; end if; return weekly (sun, mon, tue, wed, thu, fri, sat, hour24); end weekly; function friendly_date(date_string in varchar2, format_string in varchar2) return date is -- Useless wrapper around to_date begin return to_date(date_string, format_string); exception when others then debug ('Error in job_next_dates.friendly_date: Bad date or format string'); debug ( sqlerrm ); raise; end ; function specific_dates(format_string in varchar2, date1 in varchar2, date2 in varchar2 default null, date3 in varchar2 default null, date4 in varchar2 default null, date5 in varchar2 default null) return date is -- Specify up to five unrelated dates, all with same format string, -- in any order, for the job to run next_date date := null; curr_date date; currtime date; begin currtime := sysdate + 1/100000; curr_date := to_date(date1,format_string); if curr_date > currtime then next_date := curr_date; end if; curr_date := to_date(date2,format_string); if curr_date > currtime and curr_date < next_date then next_date := curr_date; end if; curr_date := to_date(date3,format_string); if curr_date > currtime and curr_date < next_date then next_date := curr_date; end if; curr_date := to_date(date4,format_string); if curr_date > currtime and curr_date < next_date then next_date := curr_date; end if; curr_date := to_date(date5,format_string); if curr_date > currtime and curr_date < next_date then next_date := curr_date; end if; return next_date; exception when others then debug ('Error in job.next_dates.specific_date: '); debug ( sqlerrm ); raise; end specific_dates; function specific_dates(format_string in varchar2, date1 in varchar2, date2 in varchar2 default null, date3 in varchar2 default null, date4 in varchar2 default null, date5 in varchar2 default null, hour24 varchar2) return date is -- Specify up to five unrelated dates, all with same format string, -- in any order, for the job to run -- PLUS, for convenience, specify an amount of time in hours (up to 23:59) -- to add to each date. This makes it easier to enter. -- Example : -- job_next_dates.specific_dates('MM/DD/YYYY','12/23/2003', -- '12/15/2003','12/22/2003', hour24 => '02:34'); retval date; hno number; begin hno := time_in_minutes(hour24); retval := specific_dates (format_string, date1, date2, date3, date4, date5) + hno/1440 ; return retval; exception when others then debug ('Error in job.next_dates.specific_dates: '); debug ( sqlerrm ); raise; end specific_dates; function every_day(hour24 in varchar2) return date is -- Return tomorrow at hour24 o'clock begin return trunc(sysdate+1) + time_in_minutes(hour24)/1440 ; exception when others then debug ('Error in job.next_dates.tomorrow: '); debug ( sqlerrm ); raise; end every_day; end job_next_dates; / ------------------------ CUT HERE ----------------------------
STANDARD DISCLAIMER: Test all code before running it on your production system. Code provided as an example for educational purposes only. Etc.
For maximum utility, I recommend that (after testing it on your system) you install this package as a user found in all your databases, then
grant execute on myuser.job_next_dates to public; create public synonym job_next_dates for myuser.job_next_dates;