How to create a scheduled job in Oracle?

2 years ago
Benjamin Taylor
4 minutes

The method of creating Job scheduled tasks in Oracle databases is as follows:

Create a new Job using the CREATE_JOB procedure in the DBMS_SCHEDULER package. For example:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           => 'my_job',
    job_type           => 'PLSQL_BLOCK',
    job_action         => 'BEGIN your_procedure(); END;',
    start_date         => SYSTIMESTAMP,
    repeat_interval    => 'FREQ=DAILY;BYHOUR=8;',
    end_date           => NULL,
    enabled            => TRUE,
    comments           => 'Job to run your_procedure() daily at 8AM');END;/

2. Create a new Program using the CREATE_PROGRAM procedure in the DBMS_SCHEDULER package. For example:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name        => 'my_program',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'your_procedure',
    enabled             => TRUE,
    comments            => 'Program to run your_procedure');END;/

3. Create a new schedule using the CREATE_SCHEDULE procedure in the DBMS_SCHEDULER package. For example:

BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name       => 'my_schedule',
    repeat_interval     => 'FREQ=DAILY;BYHOUR=8;',
    comments            => 'Schedule to run daily at 8AM');END;/

4. Use the CREATE_JOB procedure in the DBMS_SCHEDULER package to link the Job, Program, and Schedule together. For example:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name            => 'my_job',
    program_name        => 'my_program',
    schedule_name       => 'my_schedule',
    enabled             => TRUE,
    comments            => 'Job to run your_procedure daily at 8AM');END;/

This is the method of creating scheduled tasks using the DBMS_SCHEDULER package in Oracle database. You can adjust the parameters of the job according to your actual needs, such as specifying different repeat intervals, start times, etc.

bannerAds