Automating Tasks with Jobs and Scheduler in Oracle PL/SQL

Introduction

In the realm of database management, task automation is crucial for optimizing operations, reducing human errors, and ensuring efficiency in production environments. Oracle Database provides powerful tools for this through the Oracle Scheduler, primarily implemented via the PL/SQL package DBMS_SCHEDULER. This feature enables scheduling and managing tasks programmatically, such as executing stored procedures, external scripts, or PL/SQL blocks, at specific times, based on events, or dependencies.

Unlike the older DBMS_JOB, which was simpler but limited, DBMS_SCHEDULER is more robust, supporting complex schedules, resource management, and distributed execution. In this article, we’ll explore from basic to advanced concepts, with practical examples, to help you implement efficient automation in your Oracle environment. The concepts presented are based on the official Oracle Database 23c documentation, ensuring accuracy and relevance.

Basic Concepts

What is the Oracle Scheduler?

The Oracle Scheduler is a task scheduling system integrated into the Oracle Database, accessible through the DBMS_SCHEDULER package. It allows you to create, run, and manage jobs (tasks) triggered by time, events, or dependencies. Key components include:

• Jobs: The primary unit, defining what to do (action), when (schedule), and where (execution location).

• Programs: Define the action to be performed, such as a PL/SQL procedure or an external executable.

• Schedules: Specify the repetition pattern using a rich calendar syntax, e.g., “every Monday and Thursday at 3 PM.”

To use the Scheduler, you need privileges like CREATE JOB to create jobs in your schema or CREATE ANY JOB for other schemas.

Creating a Simple Job

Start with a basic job that executes a PL/SQL block. Use the CREATE_JOB procedure. Here’s an example that inserts a record into a table daily:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'MY_DAILY_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN INSERT INTO my_table (column) VALUES (SYSDATE); COMMIT; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;',
    enabled         => TRUE,
    comments        => 'Inserts current date daily at midnight'
  );
END;
/

• job_type: 'PLSQL_BLOCK' for anonymous blocks, or 'STORED_PROCEDURE' for existing procedures.

• repeat_interval: Uses syntax like 'FREQ=HOURLY; INTERVAL=2' for every 2 hours.

• To enable: DBMS_SCHEDULER.ENABLE('MY_DAILY_JOB');.

• To run immediately: DBMS_SCHEDULER.RUN_JOB('MY_DAILY_JOB');.

Managing Basic Jobs

• Modify Attributes: Use SET_ATTRIBUTE to change properties, such as the repeat interval:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    name      => 'MY_DAILY_JOB',
    attribute => 'REPEAT_INTERVAL',
    value     => 'FREQ=DAILY; BYHOUR=12'
  );
END;
/

• Disable or Drop: DBMS_SCHEDULER.DISABLE('MY_DAILY_JOB'); or DBMS_SCHEDULER.DROP_JOB('MY_DAILY_JOB');.

• Monitor: Query views like DBA_SCHEDULER_JOBS to check status:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_DAILY_JOB';

Intermediate Level: Programs, Schedules, and Job Classes

Using Programs for Modularity

Programs separate the action logic from the job, enabling reuse. Create a program and link it to a job:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name  => 'MY_PROGRAM',
    program_type  => 'STORED_PROCEDURE',
    program_action => 'MY_PROCEDURE',
    enabled       => TRUE
  );
END;
/

Then, create a job referencing the program:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name     => 'JOB_WITH_PROGRAM',
    program_name => 'MY_PROGRAM',
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON',
    enabled      => TRUE
  );
END;
/

Creating Shared Schedules

Schedules define reusable repetition patterns:

BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'WEEKLY_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=TUE,THU; BYHOUR=15; BYMINUTE=0',
    comments        => 'Tuesdays and Thursdays at 3 PM'
  );
END;
/

Associate it with a job: In CREATE_JOB, use schedule_name => 'WEEKLY_SCHEDULE'.

Job Classes for Prioritization

Job Classes group jobs for resource management. Create one:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
    job_class_name => 'PRIORITY_CLASS',
    resource_consumer_group => 'HIGH_PRIORITY',
    comments       => 'Critical jobs'
  );
END;
/

Associate it in a job: job_class => 'PRIORITY_CLASS'.

Advanced Level: Chains, Events, Credentials, and More

Chains for Dependencies

Chains enable task sequences with dependencies. Create a simple chain:

BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name => 'MY_CHAIN'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name => 'MY_CHAIN',
    step_name  => 'STEP1',
    program_name => 'PROGRAM1'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name => 'MY_CHAIN',
    step_name  => 'STEP2',
    program_name => 'PROGRAM2'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name => 'MY_CHAIN',
    condition  => 'TRUE',
    action     => 'START STEP1'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name => 'MY_CHAIN',
    condition  => 'STEP1 COMPLETED',
    action     => 'START STEP2'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name => 'MY_CHAIN',
    condition  => 'STEP2 COMPLETED',
    action     => 'END'
  );

  DBMS_SCHEDULER.ENABLE('MY_CHAIN');
END;
/

Create a job to start the chain: job_type => 'CHAIN', job_action => 'MY_CHAIN'.

Event-Based Jobs

For event-driven triggers, such as file arrivals (using File Watchers):

BEGIN
  DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
    schedule_name   => 'FILE_EVENT',
    start_date      => SYSTIMESTAMP,
    event_condition => 'tab.user_data.event_name = ''FILE_ARRIVAL''',
    queue_spec      => 'my_event_queue'
  );
END;
/

Associate it with a job via schedule_name.

Credentials and Remote Execution

Credentials store credentials for external execution:

BEGIN
  DBMS_SCHEDULER.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username        => 'os_user',
    password        => 'password'
  );
END;
/

Use in external jobs: credential_name => 'MY_CREDENTIAL'.

Advanced Monitoring and Best Practices

• Views: DBA_SCHEDULER_RUNNING_JOBS for running jobs, DBA_SCHEDULER_JOB_LOG for logs.

• Windows: For maintenance windows, create with CREATE_WINDOW.

• Best Practices: Use programs and schedules for reuse; monitor logs regularly; avoid long-running jobs that lock resources; test in development environments; consider Oracle RAC for high availability.

Conclusion

Automation with Jobs and Scheduler in Oracle PL/SQL transforms manual tasks into efficient, scalable processes, freeing up time for innovation over routine. From basic jobs to advanced chains and event-based triggers, DBMS_SCHEDULER offers flexibility for complex scenarios. Implement these features to enhance your database performance, always referring to the official Oracle documentation for version-specific adaptations. With practice, you’ll elevate automation in your projects to new heights.

#OracleScheduler #PLSQL #TaskAutomation #DBMSScheduler #OracleDatabase #JobsOracle #PLSQLProgramming #DatabaseDevelopment #TechTips #DBAutomation