Using the power of DBMS_JOB.SUBMIT

  

From: http://www.orafaq.com/node/871

 

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;