通过查阅网上文章，发现很多Advanced Queue的例子无法跑起来。
参考了英文网站，可以正常运行成功。
http://www.orafaq.com/wiki/Advanced_Queueing
第一步：
建立object，建立queue table，建立queue，然后启动queue。
*****************************************************************************
[oracle@localhost ~]$ cat q01.sql
CREATE OR REPLACE TYPE event_msg_type AS OBJECT (
name VARCHAR2(10),
current_status NUMBER(5),
next_status NUMBER(5)
);
EXECUTE DBMS_AQADM.create_queue_table( -
queue_table => 'testq.event_queue_tab', -
queue_payload_type => 'testq.event_msg_type');
EXECUTE DBMS_AQADM.create_queue( -
queue_name => 'testq.event_queue', -
queue_table => 'testq.event_queue_tab');
EXECUTE DBMS_AQADM.start_queue( -
queue_name => 'testq.event_queue', -
enqueue => TRUE);
/
[oracle@localhost ~]$
执行：
SQL> @q01.sql;
Type created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
*****************************************************************************
第二步：
执行enqueue操作。
*****************************************************************************
[oracle@localhost ~]$ cat q02.sql
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg TESTQ.event_msg_type;
BEGIN
l_event_msg := TESTQ.event_msg_type('REPORTER', 1, 2);
DBMS_AQ.enqueue(queue_name => 'testq.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
COMMIT;
END;
/
[oracle@localhost ~]$
SQL> set serveroutput on;
SQL>
SQL> @q02.sql;
PL/SQL procedure successfully completed.
SQL>
*****************************************************************************
第三步：
执行dequeue操作。
*****************************************************************************
[oracle@localhost ~]$ cat q03.sql
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg TESTQ.event_msg_type;
BEGIN
DBMS_AQ.dequeue(queue_name => 'testq.event_queue',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
DBMS_OUTPUT.put_line('Event Name : ' || l_event_msg.name);
DBMS_OUTPUT.put_line('Current Status: ' || l_event_msg.current_status);
DBMS_OUTPUT.put_line('Next Status : ' || l_event_msg.next_status);
COMMIT;
END;
/
[oracle@localhost ~]$
执行
SQL> @q03.sql;
Event Name : REPORTER
Current Status: 1
Next Status : 2
PL/SQL procedure successfully completed.
SQL>
*****************************************************************************
备忘！
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3643093.html，如需转载请自行联系原作者
