DEV Community

mrcaption49
mrcaption49

Posted on • Edited on

DBMS_SCHEDULER - CREATE_JOB | CREATE_PROGRAM | CREATE_SCHEDULE | mrcaption49

#oracle #programming #data #webdev

🔄 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)