Event Based Oracle scheduler Job

Question

I would like to create an event-based scheduled job. This job should stop whenever a scheduler generates an event of type JOB_OVER_MAX_DUR. I have written the following code, which currently only works for scheduler jobs within the admin schema. However, I intend for it to stop jobs in other schemas as well but it is not working, can you please help me is there anything which i am missing.

exec dbms_scheduler.add_event_queue_subscriber('MYAGENT');

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'TEST_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 
         'BEGIN
            -- Retrieve the job owner and job name based on the event condition
            FOR job_rec IN (SELECT owner, job_name
            FROM dba_scheduler_jobs
            where state = ''RUNNING'' and raise_events like ''%JOB_OVER_MAX_DUR%'') LOOP
            -- Stop the retrieved job
                IF job_rec.job_name IS NOT NULL THEN
                DBMS_SCHEDULER.stop_job(job_rec.owner || ''.'' || job_rec.job_name, TRUE);
                END IF;
            END LOOP;
         END;',
      event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
      queue_spec      => 'sys.scheduler$_event_queue,MYAGENT',
      enabled         => TRUE
   );
END;
/

I want the code to stop scheduler jobs that are present in other schemas also

Answer 1

It's a bit complicated....

First, you set the attribute to the job that has to raise the event before to enable/run it:

DBMS_SCHEDULER.set_attribute (name => 'Job_Name_that_Expire', attribute => 'max_run_duration', VALUE => NUMTODSINTERVAL (2, 'HOUR'));

Second, you have to create a job that "catches" the event and stops the job that sends the "expire" event:

  DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => '<schema_name_of_catcher."Catcher">'
      ,start_date      => NULL
      ,event_condition => 'tab.user_data.object_owner = ''<schema_name_of_expired>'' AND tab.user_data.object_name = ''<Job_Name_that_Expire>'' AND
       tab.user_data.event_type = ''JOB_OVER_MAX_DUR'''
      ,queue_spec      => 'SYS.SCHEDULER$_EVENT_QUEUE, <Agent_Name>'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'DBMS_SCHEDULER.STOP_JOB(''<schema_name_of_expired>."<Job_Name_that_Expire>"'',false);'
      ,comments        => NULL
    );