oracle Oracle中的PL/SQL函数看不到DBMS_AQ
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1378721/
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
PL/SQL function in Oracle cannot see DBMS_AQ
提问by Micha? Niklas
I have problem with Oracle 9.2 and JMS. I created PL/SQL routine to send XML text (from file or CLOB) to queue, but this routine do not compile.
我对 Oracle 9.2 和 JMS 有问题。我创建了 PL/SQL 例程来将 XML 文本（从文件或 CLOB）发送到队列，但该例程无法编译。
My code looks like (filling message omitted):
我的代码看起来像（省略填充消息）：
create or replace procedure jms_test(msg varchar2)
is
id pls_integer;
message sys.aq$_jms_stream_message;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid raw(16);
java_exp exception;
v_filehandle_input utl_file.file_type;
v_newline varchar2(32767);
pragma exception_init(java_exp, -24197);
begin
message := sys.aq$_jms_stream_message.construct;
message.set_string_property('FROM', 'TEST');
id := message.clear_body(-1);
end;
And Oracle raports that:
Oracle 报告说：
Error(6,21): PLS-00201: identifier 'DBMS_AQ' must be declared
I looked at some newsgroups and tried all that I found, but with no success.
我查看了一些新闻组并尝试了我找到的所有内容，但没有成功。
granted (with success) many permisions
- GRANT RESOURCE TO user;
- GRANT CONNECT TO user;
- GRANT EXECUTE ANY PROCEDURE TO user;
- GRANT aq_administrator_role TO user;
- GRANT aq_user_role TO user;
- GRANT EXECUTE ON dbms_aqadm TO user;
- GRANT EXECUTE ON dbms_aq TO user;
- GRANT EXECUTE ON dbms_aqin TO user;
desc dbms_aq shows many functions like DEQUEUE, ENQUEUE, LISTEN
executed in SQLPlus both catqueue.sql and dbmsaq.plb
授予（成功）许多权限
- 授予用户资源；
- 授予用户连接；
- 授予用户执行任何程序；
- 将 aq_administrator_role 授予用户；
- 将 aq_user_role 授予用户；
- 向用户授予 dbms_aqadm 上的执行权限；
- 授予用户执行 dbms_aq 权限；
- 向用户授予 dbms_aqin 上的执行权限；
desc dbms_aq 显示许多函数，如 DEQUEUE、ENQUEUE、LISTEN
在 SQLPlus 中执行 catqueue.sql 和 dbmsaq.plb
Anybody have idea what can be wrong?
有人知道什么是错的吗？
EDITED:
编辑：
I can do all that is described in: http://rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html, so dbms_aq is somehow visible, but not in my procedure.
我可以完成以下所有内容：http://rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html，因此 dbms_aq 以某种方式可见，但不在我的程序中。
回答by cagcowboy
Does it work if you do...
如果你这样做有用吗...
SYS.DBMS_AQ
instead of just
而不仅仅是
DBMS_AQ
If so, you're missing a synonym.
如果是这样，您就缺少同义词。
EDIT:
编辑：
If you're now getting "PLS-00201: identifier 'SYS.DBMS_AQ" then I'd double check your grants.
如果您现在收到“PLS-00201：标识符'SYS.DBMS_AQ”，那么我会仔细检查您的赠款。
GRANT EXECUTE ON SYS.DBMS_AQ to <your-user>;
Also, just to confirm, you've granted the execute privilege directlyto the user, and not via a role?
另外，只是为了确认，您已将执行权限直接授予用户，而不是通过角色？
回答by Vladimir
In order to use objects in stored procedures, you must have grants WITH GRANT OPTION.
为了在存储过程中使用对象，您必须拥有 WITH GRANT OPTION 的授权。
In this case you smust have GRANT EXECUTE ON SYS.DBMS_AQ WITH GRANT OPTIONto ;
在这种情况下，您必须将 GRANT EXECUTE ON SYS.DBMS_AQ WITH GRANT OPTION 设置为 ;
