DBMS JOB complex scheduling

This page describes two ways to schedule a job when the rule is not a simple expression that can be given in the interval parameter of the DBMS_JOB.SUBMIT function: "custom function" and "setting the internal Oracle variables".

Custom function

To determine the date of the next execution, the job coordinator executes the dynamic SQL 'select '||interval||' from dual', where "interval" is the value of the parameter of same name you gave at job submission. So this interval can be anything that returns a date and so can be a custom function.

Note: this call from the coordinator is made before the execution of the expression in what parameter.

We will apply this at 2 examples: - execute the job every week day at 7AM and on Saturday at 10AM. - execute the job every 10 minutes from Monday to Friday, from 8AM to 10AM and from 2PM to 4PM

First example

We create the following function:

create or replace function NextJobDate return date is
  now date := sysdate;
begin
  case
    when to_number(to_char(now,'D')) = to_number(to_char(to_date('03/07/2004','DD/MM/YYYY'),'D'))
      then /* Saturday */
        if to_number(to_char(now,'HH24')) < 10 then /* before 10AM -> next = 10AM */
          return trunc(now)+10/24;
        else /* after 10AM -> next = Monday */
          return trunc(now)+2+7/24;
        end if;
    when to_number(to_char(now,'D')) = to_number(to_char(to_date('04/07/2004','DD/MM/YYYY'),'D'))
      then /* Sunday -> next = Monday morning */
        return trunc(now)+1+7/24;
    when to_number(to_char(now,'HH24')) <= 7 
      then /* week day before 7AM -> next = 7AM */
        return trunc(now)+7/24;
    when to_number(to_char(now,'D')) = to_number(to_char(to_date('02/07/2004','DD/MM/YYYY'),'D'))
      then /* Friday after the hour -> next = Saturday morning */
        return trunc(now)+1+10/24;
    else /* -> weekday after the hour, next = tomorrow morning */
      return trunc(now)+1+7/24;
  end case;
end;
/

In this case, the parameter interval is set to NextJobDate.

Second example

We create the following function:

create or replace function NextJobDate (nbMin number) return date is
  /*-----------------------------------------------------------*/
  /*                                                           */
  /* Parameter : nbMin=number of minutes to the next execution */
  /*                                                           */
  /*-----------------------------------------------------------*/
  now date := sysdate;
begin
  case 
    when to_number(to_char(now,'D')) = to_number(to_char(to_date('03/07/2004','DD/MM/YYYY'),'D'))
      then /* Saturday -> next = Monday morning */
        return trunc(now)+2+8/24;
    when to_number(to_char(now,'D')) = to_number(to_char(to_date('04/07/2004','DD/MM/YYYY'),'D'))
      then /* Sunday -> next = Monday morning */
        return trunc(now)+1+8/24;
    when to_number(to_char(now,'HH24')) < 8 
      then /* before 8AM -> next = 8AM */
        return trunc(now)+8/24;
    when to_number(to_char(now,'HH24')) >= 10 and to_number(to_char(now,'HH24')) < 14 
      then /* after 10AM and before 2PM -> next = 2PM */
        return trunc(now)+14/24;
    when to_number(to_char(now,'HH24')) >= 16 
      then /* after 4PM -> next = tomorrow or next Monday 8AM */
        if to_number(to_char(now,'D')) = 
           to_number(to_char(to_date('02/07/2004','DD/MM/YYYY'),'D'))
          then /* Friday -> next = Monday morning */
            return trunc(now)+3+8/24;
          else /* Other week day -> next = tomorrow morning */
            return trunc(now)+1+8/24;
        end if;
    else /* otherwise next in nbMin minutes */
      return now+nbMin/24/60;
  end case;
end;
/

In this case, the parameter interval is set to NextJobDate(10).

Setting the internal variables

When the job coordinator has to execute a job, it first set the internal (bind) variable job with the value of SYS.JOB$.JOB column and the internal variable mydate with the result of the dynamic query "select ||interval|| from dual" where interval comes from the same SYS.JOB$ table. Then it executes the following dynamic PL/SQL block:

DECLARE 
  job       BINARY_INTEGER := :job; 
  next_date DATE           := :mydate;  
  broken    BOOLEAN        := FALSE; 
BEGIN
  <<< Value of the column SYS.JOB$.WHAT >>>
  :mydate := next_date; 
  IF broken THEN :b := 1; ELSE :b := 0; END IF; 
END;
/

As we can see the variable next_date is set to the value of the bind variable :mydate at the beginning of the block and this later bind variable is set back with the content of the former one at the end of the block, after the execution of the code inside the what column. In the end, after the execution of this PL/SQL block, the job coordinator updates the column SYS.JOB$.NEXT_DATE with the content of the bind variable :mydate.

So if the code inside what column modifies the value of the next_date variable this modification is passed on the SYS.JOB$.NEXT_DATE column.

Here's an example to see what has been said. We create a table that will contain some messages from the job and a procedure that will be executed by the job. This procedure will modify the hour of the next execution (to tomorrow 3AM) and stored in the table the date before and after modification. Then, we will create the job, query USER_JOBS view to check the NEXT_DATE column, execute the job and check again the same USER_JOBS view.

SQL> create table job_msg (msg varchar2(80));

Table created.

SQL> create or replace procedure job_proc (p_job in number, p_next_date in out date)
  2  as
  3    l_next_date date := p_next_date;
  4  begin
  5    p_next_date := trunc(sysdate)+1+3/24;
  6    insert into job_msg values (
  7      'next_date planned: "' || to_char(l_next_date,'DD/MM/YYYY HH24:MI:SS') || 
  8      '", next_date modified: "'||to_char(p_next_date,'DD/MM/YYYY HH24:MI:SS')||'"'
  9      );
 10  end;
 11  /

Procedure created.

SQL> variable j number
SQL> set null null
SQL> col interval format a10
SQL> exec dbms_job.submit(:j,'job_proc(job,next_date);');

PL/SQL procedure successfully completed.

SQL> select interval, 
  2         to_char(last_date,'DD/MM/YYYY HH24:MI:SS') last_date,
  3         to_char(next_date,'DD/MM/YYYY HH24:MI:SS') next_date
  4  from user_jobs where job = :j;

INTERVAL   LAST_DATE           NEXT_DATE
---------- ------------------- -------------------
null       null                22/04/2005 11:52:45

SQL> exec dbms_job.run(:j);

PL/SQL procedure successfully completed.

SQL> select interval, 
  2         to_char(last_date,'DD/MM/YYYY HH24:MI:SS') last_date,
  3         to_char(next_date,'DD/MM/YYYY HH24:MI:SS') next_date
  4  from user_jobs where job = :j;

INTERVAL   LAST_DATE           NEXT_DATE
---------- ------------------- -------------------
null       22/04/2005 11:52:45 23/04/2005 03:00:00

SQL> select msg from job_msg;

MSG
--------------------------------------------------------------------------------
next_date planned: "", next_date modified: "23/04/2005 03:00:00"

Note: The name of the variables passed to the job procedure are mandatory as they are the name of the variables inside the internal PL/SQL block seen above.

Note: This method is very useful when the date of the next execution is hard to compute or depends on (the result of) the current execution of the job.