Display contents of Oracle AQ SYS.AQ$_JMS_TEXT_MESSAGE
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.
So I suppose it should be:
select queue.user_data.text_vc from [queue_table] queue
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.