可以运行的Oracle Advanced Queue的例子

通过查阅网上文章，发现很多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>

*****************************************************************************