dbms_scheduler job raise_events dequeue

来源：互联网发布：专心做事知乎编辑：程序博客网时间：2024/04/26 00:51

首先需要 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 ONSQL> EXEC p_dequeue;event_type : JOB_STARTEDobject_owner : TESTobject_name : SCHEDULER_JOB_TESTevent_timestamp: 15-8月 -14 04.48.40.316000 下午 +08:00error_code : 0event_status : 1log_id : 2266run_count : 5failure_count : 0retry_count : 0PL/SQL procedure successfully completed

第二个是成功：job_succeeded

SQL> EXEC p_dequeue;event_type : JOB_SUCCEEDEDobject_owner : TESTobject_name : SCHEDULER_JOB_TESTevent_timestamp: 15-8月 -14 04.48.40.675000 下午 +08:00error_code : 0event_status : 0log_id : 2266run_count : 5failure_count : 0retry_count : 0PL/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

0 0