What's in my JMS queue?

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.

To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in that queue table:

rwijk@ORA11GR1> begin
 2    dbms_aqadm.create_queue_table
 3    ( queue_table        => 'my_queue_table'
 4    , queue_payload_type => 'sys.aq$_jms_text_message'
 5    );
 6    dbms_aqadm.create_queue
 7    ( queue_name  => 'my_queue'
 8    , queue_table => 'my_queue_table'
 9    );
10    dbms_aqadm.start_queue
11    ( queue_name => 'my_queue'
12    );
13  end;
14  /

PL/SQL procedure successfully completed.

Next, put a message on the queue:

rwijk@ORA11GR1> declare
 2    l_enqueue_options    dbms_aq.enqueue_options_t;
 3    l_message_properties dbms_aq.message_properties_t;
 4    l_message            sys.aq$_jms_text_message;
 5    l_msgid              raw(16);
 6  begin
 7    l_message := sys.aq$_jms_text_message.construct;
 8    l_message.set_text(xmltype('<emp><ename>ROB</ename></emp>').getClobVal());
 9    dbms_aq.enqueue
10    ( queue_name         => 'my_queue'
11    , enqueue_options    => l_enqueue_options
12    , message_properties => l_message_properties
13    , payload            => l_message
14    , msgid              => l_msgid
15    );
16    commit;
17  end;
18  /

PL/SQL procedure successfully completed.

You can query a queue by querying the queue table and filtering on the q_name column, like this:

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , user_data
 5    from my_queue_table
 6   where q_name = 'MY_QUEUE'
 7  /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA(HEADER(REPLYTO(NAME, ADDRESS, PROTOCOL), TYPE, USERID, APPID, GROUPID,
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
AQ$_JMS_TEXT_MESSAGE(AQ$_JMS_HEADER(NULL, NULL, NULL, NULL, NULL, NULL, NULL), 2
9, '<emp><ename>ROB</ename></emp>', NULL)


1 row selected.

At work I use an older SQL*Plus version, that is not able to show the contents of the user_data column, because of some LOB content. No problem, as I am really only interested in the text_vc field of the sys.aq$_jms_text_message object. So just query that field:

rwijk@ORA11GR1> desc my_queue_table
Name                                      Null?    Type
----------------------------------------- -------- ------------------------
Q_NAME                                             VARCHAR2(30)
MSGID                                     NOT NULL RAW(16)
CORRID                                             VARCHAR2(128)
PRIORITY                                           NUMBER
STATE                                              NUMBER
DELAY                                              TIMESTAMP(6)
EXPIRATION                                         NUMBER
TIME_MANAGER_INFO                                  TIMESTAMP(6)
LOCAL_ORDER_NO                                     NUMBER
CHAIN_NO                                           NUMBER
CSCN                                               NUMBER
DSCN                                               NUMBER
ENQ_TIME                                           TIMESTAMP(6)
ENQ_UID                                            VARCHAR2(30)
ENQ_TID                                            VARCHAR2(30)
DEQ_TIME                                           TIMESTAMP(6)
DEQ_UID                                            VARCHAR2(30)
DEQ_TID                                            VARCHAR2(30)
RETRY_COUNT                                        NUMBER
EXCEPTION_QSCHEMA                                  VARCHAR2(30)
EXCEPTION_QUEUE                                    VARCHAR2(30)
STEP_NO                                            NUMBER
RECIPIENT_KEY                                      NUMBER
DEQUEUE_MSGID                                      RAW(16)
SENDER_NAME                                        VARCHAR2(30)
SENDER_ADDRESS                                     VARCHAR2(1024)
SENDER_PROTOCOL                                    NUMBER
USER_DATA                                          SYS.AQ$_JMS_TEXT_MESSAGE
USER_PROP                                          SYS.ANYDATA

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , user_data.text_vc
 5    from my_queue_table
 6   where q_name = 'MY_QUEUE'
 7  /
    , user_data.text_vc
      *
ERROR at line 4:
ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier

And this message puzzled me for a quite a while. After an extensive search, an AskTom thread provided the answer. A very simple answer: you need to prefix the expression with a table alias:

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , qt.user_data.text_vc
 5    from my_queue_table qt
 6   where q_name = 'MY_QUEUE'
 7  /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA.TEXT_VC
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
<emp><ename>ROB</ename></emp>


1 row selected.