SELECT * FROM ALL_SCHEDULER_JOBS ;

select *

FROM ALL_SCHEDULER_JOB_LOG

order by log_date desc;

 

select *

from all_SCHEDULER_JOB_RUN_DETAILS

order by log_date desc;

set serveroutput on;

declare

    isPresent number := 0;

  l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';

  l_scanExcptionCode varchar2(255) := 'XXIC_JMS_UTILS.MoveExceptionsToErrorQueue;';

  l_ExceptionScanInterval varchar2(255) := 'FREQ=MINUTELY';

 

begin

  SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;

  if (isPresent <>1 ) then

    dbms_output.put_line('Createing schedular job');  

   

    Dbms_scheduler.create_job (

      job_name   => l_scanExcptionJobName,

      job_type   => 'PLSQL_BLOCK',

      job_action => l_scanExcptionCode,

      start_date      => sysdate,

      repeat_interval => l_ExceptionScanInterval,

      enabled         => TRUE,

      comments        => 'This process deos something'

    );

end;

set serveroutput on;

declare

    isPresent number := 0;

  l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';

begin

    SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;

    if (isPresent<>0) then

      dbms_output.put_line('Deleting Exception -> Error Job');

      DBMS_SCHEDULER.DROP_JOB(l_scanExcptionJobName);

    end if;

end;