π Oracle DBMS_SCHEDULER β Full Guide to CREATE_JOB, CREATE_PROGRAM, and CREATE_SCHEDULE
Oracleβs DBMS_SCHEDULER package offers powerful scheduling capabilities, enabling the automation of PL/SQL procedures, scripts, and complex workflows.
This post explains both modular and direct approaches to job scheduling using:
- CREATE_PROGRAM
- CREATE_SCHEDULE
- CREATE_JOB
π§ Understanding the Two Approaches
1. Modular Method (Recommended for Reusability)
Separate definitions for:
- Logic β via CREATE_PROGRAM
- Timing β via CREATE_SCHEDULE
- Both are linked using CREATE_JOB.
2. Direct Method
All job logic and schedule are defined inline within a single CREATE_JOB call β best for one-off or simpler tasks.
π οΈ Approach 1: Direct Job Creation using DBMS_SCHEDULER.CREATE_JOB
This approach bundles everything β logic and timing β in one call.
β
Code Example
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'CGO_OWNR.REVERA_INT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
ngcs_revera_interface_pg.REV_INT_MAIN(''SK'', ''NGCSUTL'', TRUE);
COMMIT;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=50;BYSECOND=0',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => FALSE,
auto_drop => FALSE,
comments => 'Runs Revera interface for SK daily at 01:50 AM'
);
END;
/
π Notes:
- Quick to implement.
- Not ideal for reuse β logic and schedule are tightly coupled.
π§© Approach 2: Modular Job Creation Using PROGRAM and SCHEDULE This method breaks the process into 3 clear components.
π Step 1: Create the Program
Defines what should run.
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'CGO_OWNR.REVERA_INT_PRG',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
ngcs_revera_interface_pg.REV_INT_MAIN(''SK'', ''NGCSUTL'', TRUE);
COMMIT;
END;',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Program to execute REVERA interface for SK'
);
END;
/
β° Step 2: Create the Schedule
Defines when it should run.
BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name => 'CGO_OWNR.REVERA_INT_SCHED',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=50;BYSECOND=0',
comments => 'Runs daily at 01:50 AM'
);
END;
/
π Step 3: Create the Job
Binds the program and schedule together.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'CGO_OWNR.REVERA_INT_JOB',
program_name => 'CGO_OWNR.REVERA_INT_PRG',
schedule_name => 'CGO_OWNR.REVERA_INT_SCHED',
job_class => 'DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Revera INT job using reusable program and schedule'
);
END;
/
π‘ Summary:
- You're still using CREATE_JOB, but you're not defining the logic (job_action) or timing (repeat_interval) directly.
- Instead, youβre referencing existing PROGRAM and SCHEDULE objects.
- This makes your job definitions cleaner, more reusable, and easy to maintain in large systems.
Top comments (0)