A job can hold everything in one definition, but the Scheduler also lets you split it into reusable parts. A program says what to run, with its arguments; a schedule says when; and a job joins a program and a schedule, with the argument values for that job. Many jobs can share one program or one schedule.
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_scheduler.create_program(program_name, program_type, program_action,
number_of_arguments => n);
dbms_scheduler.define_program_argument(program_name, position, argument_name, type);
dbms_scheduler.create_schedule(schedule_name, start_date, repeat_interval);
dbms_scheduler.create_job(job_name, program_name => ..., schedule_name => ...);
dbms_scheduler.set_job_argument_value(job_name, argument_name, value);A program with arguments is created disabled; enable it after defining them.
The example runs a procedure that logs the number of routes from an airport:
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;
/Program, Schedule, and Job
Example:
begin
dbms_scheduler.create_program(
program_name => 'ROUTE_COUNT',
program_type => 'STORED_PROCEDURE',
program_action => 'LOG_ROUTE_COUNT',
number_of_arguments => 1);
dbms_scheduler.define_program_argument('ROUTE_COUNT', 1, 'P_ORIGIN', 'VARCHAR2');
dbms_scheduler.enable('ROUTE_COUNT');
dbms_scheduler.create_schedule(
schedule_name => 'EVERY_MONDAY_6AM',
start_date => timestamp '2027-01-01 00:00:00 Asia/Dubai',
repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=6; BYMINUTE=0');
dbms_scheduler.create_job('ROUTES_DXB', program_name => 'ROUTE_COUNT',
schedule_name => 'EVERY_MONDAY_6AM');
dbms_scheduler.set_job_argument_value('ROUTES_DXB', 'P_ORIGIN', 'DXB');
dbms_scheduler.enable('ROUTES_DXB');
dbms_scheduler.run_job('ROUTES_DXB');
end;
/
select message from job_log where job_name = 'ROUTES_DXB';
select job_name, program_name, schedule_name, state, next_run_date
from user_scheduler_jobs
where job_name = 'ROUTES_DXB';Output:
PL/SQL procedure successfully completed. MESSAGE _____________________ 20 routes from DXB JOB_NAME PROGRAM_NAME SCHEDULE_NAME STATE NEXT_RUN_DATE _____________ _______________ ___________________ ____________ __________________________________ ROUTES_DXB ROUTE_COUNT EVERY_MONDAY_6AM SCHEDULED 04-JAN-2027 06:00:00 ASIA/DUBAI
- ROUTE_COUNT runs LOG_ROUTE_COUNT with one argument, P_ORIGIN.
- EVERY_MONDAY_6AM is a schedule any job can use.
- ROUTES_DXB joins the two and sets P_ORIGIN to DXB. RUN_JOB runs it once now, and it logs 20 routes from Dubai.
A second job, ROUTES_LHR, would need only CREATE_JOB and a different argument value.
Things to Know
- Changing a schedule changes every job that uses it, which keeps related jobs in step.
- DEFINE_PROGRAM_ARGUMENT can give a default value, so jobs set only the arguments that differ.
- Drop jobs before the programs and schedules they use, or use FORCE.
Cleanup:
exec dbms_scheduler.drop_job('ROUTES_DXB')
exec dbms_scheduler.drop_program('ROUTE_COUNT')
exec dbms_scheduler.drop_schedule('EVERY_MONDAY_6AM')
drop procedure log_route_count;
drop table job_log purge;Related Guides
- How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB
- How to Write Scheduler Calendar Expressions in Oracle
Conclusion
Scheduler programs describe what to run and with which arguments, schedules describe when, and jobs combine them with argument values. Split jobs this way when several share the same code or the same timing.
