15

I have an application that uses JMS op top of Oracle advanced queuing. I would like to do a query on the queue table that shows the content of the message (which in my case is XML). So when I do a 'select user_data from [queue_table]' I get 'AQ SYS.AQ$_JMS_TEXT_MESSAGE' as a response.

Is there a function so that the contents of this message can be shown? Something like 'select FUNCTION(user_data) from [queue_table]' or something?

I googled, scanned numerous Oracle articles about queuing, but I cannot find this thing. I suspect there is a simple way to do this, but I cannot find it.

Bruno Ranschaert
  • 7,428
  • 5
  • 36
  • 46

5 Answers5

15

I struggled with this one as well. I've written an answer here: http://rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html .

Regards, Rob.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
  • 2
    "select queue.user_data.text_vc from [queue_table] queue" I added the answer here, so I can promote the answer as the solution to the problem, and I want the answer to be complete. – Bruno Ranschaert Jun 23 '09 at 22:43
  • 1
    I have found that larger text are stored in queue.user_data.text_lob instead of text_vc – Michał Niklas Sep 11 '09 at 11:35
9

So I suppose it should be:

select queue.user_data.text_vc from [queue_table] queue
Bruno Ranschaert
  • 7,428
  • 5
  • 36
  • 46
  • 2
    Exactly. You should use an alias or the table_name itself in front of the user_data.text_vc. Otherwise it starts looking for a package user_data containing a function called text_vc, resulting in the error message ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier – Rob van Wijk Jun 20 '09 at 12:42
3

The answers here don't handle the display of larger contents, stored in user_data.text_lob. If the content is larger than a certain amount of bytes (4000?), text_vc will be null and you have to look at text_lob (which would be null otherwise)

In order to show all data, regardless it's size, you could use the following query using nvl:

SELECT nvl(q.user_data.text_vc, q.user_data.text_lob) FROM [queue_table] q

I guess you could (and should consider to) use coalesce instead of nvl, because it doesn't evaluate the second argument, if the first one is already different to null, but I haven't tested that one yet.

Community
  • 1
  • 1
stuXnet
  • 4,309
  • 3
  • 22
  • 31
1

Addition to the comment of stuXnet:

SELECT nvl(to_clob(q.user_data.text_vc), q.user_data.text_lob) FROM queue_table q;

without to_clob you will get ORA-22835 for data larger than 4000 chars because it preserves the space of the first argument, which is only VARCHAR2.

untitled1
  • 21
  • 3
-1

Please try the following command:

select user_data from [queue_table] name

Juno
  • 1