PL/SQL: Using Oracle DBMS_JOB For Scheduled Tasks

In order to improve performance when reporting we like to pre-build some of the data for the reports. In order to keep this data fresh we have written a simple PL/SQL script that we need to schedule to run daily.

The PL/SQL behind this is simply;

begin
  for v_DateRange in (SELECT TRUNC(SYSDATE) - 7 Start_Date,
                             TRUNC(SYSDATE) End_Date
                        FROM DUAL) loop
    gl_je_sla_update.processdaterange(p_startdate => v_DateRange.Start_Date,
                                      p_enddate   => v_DateRange.End_Date);
  end loop;
end;

This just calls the "ProcessDateRange" procedure in GL_JE_SLA_UPDATE with the date range specified by the query.

One of the key features we're after is that the job should run at 7am every day, here is the script;

begin
  sys.dbms_job.submit(

    job => :job,
    what => 'begin
      for v_DateRange in (SELECT TRUNC(SYSDATE) - 7 Start_Date,
                                 TRUNC(SYSDATE) End_Date
                            FROM DUAL) loop
        gl_je_sla_update.processdaterange(

          p_startdate => v_DateRange.Start_Date,
          p_enddate   => v_DateRange.End_Date);
  end loop;
end;',
      next_date => to_date('05-07-2012 14:38:35', 'dd-mm-yyyy hh24:mi:ss'),
      interval => 'TRUNC(SYSDATE)+1+7/24');
  commit;
end;

Submitting this job (with these values) causes the job to be run immediately as the "next date" will be in the past for you (but that's not usually a problem, but is something you should be aware of). You'll notice that in order to use this PL/SQL you'll need to work with the :job output variable.

A full list of the available options on the DBMS_JOB.Submit procedure is;

Parameters for DBMS_JOB.Submit

 As you can see the only required input parameter is what, next_date will default to "Now", the interval will default to NULL (i.e. do not repeat), no_parse defaults to false (don't not parse - arrrgh!), instance defaults to 0 (the instance you're currently running on), and force defaults to false.

I have never used no_parse, instance (we only have one), and force.

If you view the source on the package header there is some other useful suggestions and explanations.