DBMS_SCHEDULER

Submitted by code_admin on Tue, 07/17/2018 - 14:02

This is a replacement for DBMS_JOBS

What jobs are there

  1. SELECT * FROM ALL_SCHEDULER_JOBS ;

Viewing job details

  1. select *
  2. FROM ALL_SCHEDULER_JOB_LOG
  3. order by log_date desc;
  4.  
  5. select *
  6. from all_SCHEDULER_JOB_RUN_DETAILS
  7. order by log_date desc;

Add a job

  1. set serveroutput on;
  2. declare
  3.     isPresent number := 0;
  4.   l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';
  5.   l_scanExcptionCode varchar2(255) := 'XXIC_JMS_UTILS.MoveExceptionsToErrorQueue;';
  6.   l_ExceptionScanInterval varchar2(255) := 'FREQ=MINUTELY';
  7.  
  8. begin
  9.   SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;
  10.   if (isPresent <>1 ) then
  11.     dbms_output.put_line('Createing schedular job');  
  12.    
  13.     Dbms_scheduler.create_job (
  14.       job_name   => l_scanExcptionJobName,
  15.       job_type   => 'PLSQL_BLOCK',
  16.       job_action => l_scanExcptionCode,
  17.       start_date      => sysdate,
  18.       repeat_interval => l_ExceptionScanInterval,
  19.       enabled         => TRUE,
  20.       comments        => 'This process deos something'
  21.     );
  22. end;
Freq Code
Hourly FREQ=HOURLY;
Every Minute FREQ=MINUTELY;
Every 5 minutes freq=MINUTELY;interval=5;
Every Day at 22:30:00 freq=daily;byhour=22;byminute=30;bysecond=0;
Every Week FREQ=WEEKLY;
Every Month FREQ=MONTHLY;

http://psoug.org/reference/dbms_scheduler.html

Remove a job

  1. set serveroutput on;
  2. declare
  3.     isPresent number := 0;
  4.   l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';
  5. begin
  6.     SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;
  7.     if (isPresent<>0) then
  8.       dbms_output.put_line('Deleting Exception -> Error Job');
  9.       DBMS_SCHEDULER.DROP_JOB(l_scanExcptionJobName);
  10.     end if;
  11. end;

Tags

RJM Article Type
Quick Reference