显示 Oracle AQ SYS.AQ$_JMS_TEXT_MESSAGE 的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1020165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display contents of Oracle AQ SYS.AQ$_JMS_TEXT_MESSAGE
提问by Bruno Ranschaert
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.
我有一个使用 Oracle 高级排队的 JMS op top 的应用程序。我想对显示消息内容的队列表进行查询（在我的情况下是 XML）。因此，当我执行“从 [queue_table] 中选择 user_data”时，我会收到“AQ SYS.AQ$_JMS_TEXT_MESSAGE”作为响应。
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?
是否有功能可以显示此消息的内容？诸如“从 [queue_table] 中选择 FUNCTION(user_data)”之类的东西？
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.
我用谷歌搜索，扫描了许多关于排队的 Oracle 文章，但我找不到这个东西。我怀疑有一种简单的方法可以做到这一点，但我找不到它。
采纳答案by Rob van Wijk
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.
我也为此挣扎过。我在这里写了一个答案：http: //rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html。
Regards, Rob.
问候，罗布。
回答by Bruno Ranschaert
So I suppose it should be:
所以我想应该是：
select queue.user_data.text_vc from [queue_table] queue
回答by stuXnet
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_vcwill be nulland you have to look at text_lob(which would be nullotherwise)
此处的答案不处理存储在user_data.text_lob. 如果内容大于一定数量的字节（4000？），text_vc将是null并且您必须查看text_lob（null否则将是）
In order to show all data, regardless it's size, you could use the following query using nvl:
为了显示所有数据，无论其大小，您都可以使用以下查询nvl：
SELECT nvl(q.user_data.text_vc, q.user_data.text_lob) FROM [queue_table] q
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 coalesceinstead 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.
我想您可以（并且应该考虑）使用coalesce代替nvl，因为它不会评估第二个参数，如果第一个参数已经与 不同null，但我还没有测试过那个。
回答by untitled1
Addition to the comment of stuXnet:
除了 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.
如果没有 to_clob，对于大于 4000 个字符的数据，您将获得 ORA-22835，因为它保留了第一个参数的空间，该参数仅为 VARCHAR2。
回答by Juno
Please try the following command:
请尝试以下命令：
select user_data from [queue_table] name
从 [queue_table] 名称中选择 user_data
