DBMS_JOB is the job package that came before the Scheduler, and much older code still uses it. It still works, but in recent releases every DBMS_JOB job is created as a Scheduler job behind the scenes. Knowing how the two connect helps when you maintain or migrate old code.
Code for This Guide
The main examples are in the examples/pkg-scheduler folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_job.submit(job_out, what => 'plsql;', next_date => date,
interval => 'date expression');
dbms_job.remove(job);
dbms_job.broken(job, true);
commit; -- submitting and removing are transactionalWHAT is PL/SQL text ending in a semicolon. INTERVAL is a date expression, as text, evaluated after each run to give the next run date.
The example submits a procedure that logs route counts:
Setup:
create table job_log (logged_at timestamp default systimestamp,
job_name varchar2(30), message varchar2(200));
create or replace procedure log_route_count (p_origin varchar2) is
begin
insert into job_log (job_name, message)
select 'ROUTES_' || p_origin, count(*) || ' routes from ' || p_origin
from routes where origin = p_origin;
commit;
end;
/Submit and Remove a Job
Example:
declare
v_job binary_integer;
begin
dbms_job.submit(v_job, what => 'log_route_count(''LHR'');',
next_date => sysdate + 1, interval => 'trunc(sysdate) + 1 + 2/24');
commit;
end;
/
select what, interval, broken from user_jobs;
select job_style, job_action, repeat_interval from user_scheduler_jobs
where job_name like 'DBMS_JOB$%';
begin
for j in (select job from user_jobs) loop
dbms_job.remove(j.job);
end loop;
commit;
end;
/Output:
PL/SQL procedure successfully completed.
WHAT INTERVAL BROKEN
__________________________ ____________________________ _________
log_route_count('LHR'); trunc(sysdate) + 1 + 2/24 N
JOB_STYLE JOB_ACTION REPEAT_INTERVAL
____________ __________________________ ____________________________
REGULAR log_route_count('LHR'); trunc(sysdate) + 1 + 2/24
PL/SQL procedure successfully completed.SUBMIT creates a job that runs the procedure tomorrow and then every day at 02:00. USER_JOBS shows it the old way, and USER_SCHEDULER_JOBS shows the same job as a Scheduler job named DBMS_JOB$_ followed by its number. The block at the end removes every DBMS_JOB job and commits.
Moving to the Scheduler
| DBMS_JOB | DBMS_SCHEDULER |
|---|---|
| SUBMIT | CREATE_JOB |
| INTERVAL date expression | REPEAT_INTERVAL calendar expression |
| BROKEN | DISABLE |
| RUN | RUN_JOB |
| REMOVE | DROP_JOB |
Things to Know
- DBMS_JOB calls take effect only after COMMIT, unlike DBMS_SCHEDULER calls, which commit themselves.
- Because of that, a job submitted inside a transaction runs only if the transaction commits, which some old code relies on.
- Write new code with DBMS_SCHEDULER: it has calendars, programs, chains, logging, and notifications.
Related Guides
- How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB
- How to Write Scheduler Calendar Expressions in Oracle
Conclusion
DBMS_JOB still submits, runs, and removes jobs, now as Scheduler jobs underneath. Remember that it needs a COMMIT, read its jobs in USER_JOBS or USER_SCHEDULER_JOBS, and move them to DBMS_SCHEDULER when you can.
See my articles more often when you search Google.
