Tuesday, February 10, 2009

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.

9 comments:

  1. I have found that larger text are stored in text_lob instead of text_vc

    ReplyDelete
  2. I know, but thanks for filling up the omission.

    ReplyDelete
  3. Thanks, very useful information.
    You can also get header info, when you append the header-subfields.
    For example:

    qt.user_data.header.replyto
    qt.user_data.header.type
    qt.user_data.header.userid
    qt.user_data.header.appid
    qt.user_data.header.groupid
    qt.user_data.header.properties

    ReplyDelete
  4. is there any way to see the properties set in the header.properties field?

    ReplyDelete
  5. yes you can:
    qt.user_data.header.get_string_property ('propertyname')

    ReplyDelete
    Replies
    1. I am not able to get the property by using qt.user_data.header.get_string_property ('propertyname'). I am getting the following error:

      ORA-00904: "Q_SIH_DOWNSTREAM_QT"."USER_DATA"."HEADER": invalid identifier
      00904. 00000 - "%s: invalid identifier"
      *Cause:
      *Action:
      Error at Line: 3 Column: 7

      The query I am firing is

      select q_sih_downstream_qt.user_data.header.get_string_property('MESSAGE_TYPE') from q_sih_downstream_qt

      Delete
  6. Thanks for that, incredibly useful!

    ReplyDelete
  7. Thanks so much for posting this

    ReplyDelete