PL/SQL create job - Oracle PL/SQL Tutorial

In Oracle PL/SQL, you can create a scheduled job to execute a stored procedure at specified intervals using the DBMS_SCHEDULER package. This package provides a powerful way to manage and automate various database tasks. Here’s a step-by-step guide on how to create a job that runs a procedure:

Create or Identify Your Procedure

First, make sure you have a stored procedure that you want to schedule. This procedure should already be defined in your database.

CREATE OR REPLACE PROCEDURE my_procedure AS
BEGIN
    -- Your procedure code here
END;

Create a Job Definition

You’ll need to create a job definition using the DBMS_SCHEDULER.CREATE_JOB procedure. This job definition specifies the name of the job, the type of job (PL/SQL block in this case), and other attributes.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'my_job',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN my_procedure; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=12; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE
    );
END;
/

job_name: Provide a unique name for your job.
job_type: Set it to ‘PLSQL_BLOCK’ as we are running a PL/SQL block.
job_action: Specify the PL/SQL block to run, which includes calling your procedure.
start_date: Set the initial date and time for the job to start.
repeat_interval: Define the frequency and schedule for the job. In this example, the job runs daily at 12:00 PM.
enabled: Set it to TRUE to enable the job immediately.

Manage and Monitor the Job

Once the job is created, you can manage and monitor it using various DBMS_SCHEDULER procedures and views. For example:

To disable a job: DBMS_SCHEDULER.DISABLE(‘my_job’);
To enable a job: DBMS_SCHEDULER.ENABLE(‘my_job’);
To drop a job: DBMS_SCHEDULER.DROP_JOB(‘my_job’);

View Job Details

You can query the DBA_SCHEDULER_JOBS view to see details about your scheduled job.

SELECT 
job_name, job_type, job_action, 
start_date, repeat_interval, enabled
FROM DBA_SCHEDULER_JOBS
WHERE job_name = 'my_job';

Monitor Job Execution

You can also view the job’s execution history in the DBA_SCHEDULER_JOB_LOG view to check if your procedure is running as expected.

SELECT job_name, log_date, status
FROM DBA_SCHEDULER_JOB_LOG
WHERE job_name = 'my_job';

That’s it! You’ve created a scheduled job to run a procedure in Oracle PL/SQL. This allows you to automate various database tasks and ensure that your procedures are executed at specific intervals.

Learn PL/SQL