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.