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.

Oracle PL/SQL: Working With Oracle Jobs (Showing/ Deleting/ Adding)

Showing All Oracle Jobs
To see a list of the currently configured Oracle Jobs use the SQL;

SELECT job,
       schema_user,
       last_date,
       last_sec,
       next_date,
       next_sec,
       broken,
       interval,
       failures,
       what
  FROM dba_jobs
 ORDER BY next_date DESC

This will produce something that looks like (in SQL Developer);

Query Results in Oracle SQL Developer

The interesting parts of this statement are the two fields BROKEN and WHAT. If the job has failed to run (i.e. raise a PL/SQL error) then it's BROKEN status will be set to Y (as you can see in the top-row of the result set image above).

The WHAT field contains the SQL that the job is going to periodically run - this is stored as a large object so you might want to use an editor like SQL Developer or PL/SQL Developer to display it correctly.

Removing An Existing Job
To remove a job you need the job_id from the first query and then execute;

exec dbms_job.remove(job);

Where "job" is the value from the first query (a unique reference for the job). You then need to do a commit.

Adding A New Job
Let's assume we have the following PL/SQL block we'd like executed at 8am every day;


BEGIN
  FOR v_Row IN (SELECT SYSDATE FROM DUAL) LOOP
    dbms_output.put_line(v_Row.sysdate);
  END LOOP;
END;

This is a pretty simple PL/SQL block that will jsut write todays date to the DBMS_OUTPUT queue. Basically it won't do anything except run without an errors.

To schedule this as a job execute the following PL/SQL;



declare
  v_Job BINARY_INTEGER;
begin
  sys.dbms_job.submit(job       => v_Job,
                      what      => 'BEGIN FOR v_Row IN (SELECT SYSDATE FROM DUAL) LOOP dbms_output.put_line(v_Row.sysdate); END LOOP; END;',
                      next_date => TRUNC(SYSDATE) + 1 + 8 / 24,
                      interval  => 'TRUNC(SYSDATE) + 1 + 8/24');
end;

The only required parameters for the DBMS_JOB.Submit function are the output parameter (for the Job reference) and the what. Everything else has defaults.