DEV Community

Pranav Bakare
Pranav Bakare

Posted on • Edited on

DBMS_SCHEDULER - CREATE_JOB | CREATE_PROGRAM | CREATE_SCHEDULE | mrcaption49

πŸ”„ 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;
/
Enter fullscreen mode Exit fullscreen mode

πŸ“ 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;
/
Enter fullscreen mode Exit fullscreen mode

⏰ 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;
/
Enter fullscreen mode Exit fullscreen mode

πŸ”— 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;
/
Enter fullscreen mode Exit fullscreen mode

πŸ’‘ 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)