Using the power of DBMS_JOB.SUBMIT

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.
next_date is the next date you want the job to run. An argument of DATE datatype must be passed in.
interval is the parameter that affords the DBA great flexibility. The argument passed in is in varchar2, and it must be an expression that evaluates to a date. Each time the job is run, interval is reevaluated and the result is set as the next_date the job will run.

next_date

This parameter is easy to understand and supply. You can supply it with a to_date cast of a char string, as in,

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

The date passed in must, obviously, be greater than or equal to sysdate.

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".

interval

The power of this parameter is that it is a varchar2 string, not a date or a number of days or minutes. You may pass any varchar2 string you like; the only constraint is that the argument must evaluate to a date greater than the date on which the job is run. Eg:

'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 run

Each 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 then
    --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;
/

Don't worry, I've done the maths for you

So we've established that Oracle's dbms_job.submit supplied procedure is, indeed, just as powerful as you want it to be. By now we've also established that it can be a major pain in the tushie to use. If you have many jobs that require complex schedules, you can wind up with a litter of disorganized functions; worse, a solid knowledge of and comfort with Oracle's date arithmetic and functions are required to really make use of the flexibility of dbms_job.submit.

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:

weekly(daystring,hour24) accepts a string of 2-letter day abbreviations, and a string expressing the time in 24-hour format. For example:
job_next_dates.weekly('MoTuFri','23:00')
supplied as the INTERVAL argument to job_next_dates, will cause your job to be run Mondays, Tuesdays and Fridays at 11 PM.

specific_dates accepts up to 5 specific dates on which you want a job to run (in addition to the next_date you supply to dbms_job.submit). For example:
job_next_dates.specific_dates('mm/dd/rr hh24:mi', '12/23/04 23:11', '11/14/2004 03:33')
will run your job on the specified dates only.

every_day(hour24) will run your job every day at the time specified. eg: job_next_dates.every_day(11:34)

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.

job_next_dates package

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;
/

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;

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.