dbms_scheduler job raise_events dequeue

首先需要 DBMS_SCHEDULER.add_event_queue_subscriber ，否则无法使用

SQL> EXEC DBMS_SCHEDULER.remove_event_queue_subscriber;
PL/SQL procedure successfully completed

设置前面的job，使它执行时抛出事件，为了便于测试，改为手动执行：

BEGIN
  dbms_scheduler.disable('SCHEDULER_JOB_TEST');
  dbms_scheduler.set_attribute('SCHEDULER_JOB_TEST',
                               'raise_events',
                               dbms_scheduler.job_all_events);
  dbms_lock.sleep(2);
END;
/

建立一个proc，用来提取抛出的队列信息

CREATE OR REPLACE PROCEDURE p_dequeue AS
  l_dequeue_options    dbms_aq.dequeue_options_t;
  l_message_properties dbms_aq.message_properties_t;
  l_message_handle     RAW(16);
  l_queue_msg          sys.scheduler$_event_info;
BEGIN
  l_dequeue_options.consumer_name := USER;

  dbms_aq.dequeue(queue_name         => 'SYS.SCHEDULER$_EVENT_QUEUE',
                  dequeue_options    => l_dequeue_options,
                  message_properties => l_message_properties,
                  payload            => l_queue_msg,
                  msgid              => l_message_handle);
  COMMIT;
  dbms_output.put_line('event_type : ' || l_queue_msg.event_type);
  dbms_output.put_line('object_owner : ' || l_queue_msg.object_owner);
  dbms_output.put_line('object_name : ' || l_queue_msg.object_name);
  dbms_output.put_line('event_timestamp: ' || l_queue_msg.event_timestamp);
  dbms_output.put_line('error_code : ' || l_queue_msg.error_code);
  dbms_output.put_line('event_status : ' || l_queue_msg.event_status);
  dbms_output.put_line('log_id : ' || l_queue_msg.log_id);
  dbms_output.put_line('run_count : ' || l_queue_msg.run_count);
  dbms_output.put_line('failure_count : ' || l_queue_msg.failure_count);
  dbms_output.put_line('retry_count : ' || l_queue_msg.retry_count);
END;
/

手动执行job

SQL> EXEC dbms_scheduler.run_job('SCHEDULER_JOB_TEST');
PL/SQL procedure successfully completed

查看队列信息，会有两个信息，第一个是启动(job_started)

SQL> SET serveroutput ON
SQL> EXEC p_dequeue;
event_type : JOB_STARTED
object_owner : TEST
object_name : SCHEDULER_JOB_TEST
event_timestamp: 15-8月 -14 04.48.40.316000 下午 +08:00
error_code : 0
event_status : 1
log_id : 2266
run_count : 5
failure_count : 0
retry_count : 0
PL/SQL procedure successfully completed

第二个是成功：job_succeeded

SQL> EXEC p_dequeue;
event_type : JOB_SUCCEEDED
object_owner : TEST
object_name : SCHEDULER_JOB_TEST
event_timestamp: 15-8月 -14 04.48.40.675000 下午 +08:00
error_code : 0
event_status : 0
log_id : 2266
run_count : 5
failure_count : 0
retry_count : 0
PL/SQL procedure successfully completed

测试结束， REMOVE_EVENT_QUEUE_SUBSCRIBER  

SQL> EXEC DBMS_SCHEDULER.remove_event_queue_subscriber;
PL/SQL procedure successfully completed

信息参考： http://www.oracle-base.com/articles/10g/scheduler-enhancements-10gr2.php