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 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.
