Oracle中的PL/SQL函数看不到DBMS_AQ [英] PL/SQL function in Oracle cannot see DBMS_AQ

问题描述

我在使用Oracle 9.2和JMS时遇到问题. 我创建了PL/SQL例程，以将XML文本(来自文件或CLOB)发送到队列， 但是此例程无法编译.

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.

我的代码如下(省略填充信息):

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;

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.

1. (成功地)授予了许多权限

1. granted (with success) many permisions

• 向用户提供GRANT资源；
• GRANT CONNECT TO user;
• GRANT对用户执行任何程序；
• GRANT aq_administrator_role TO用户；
• GRANT aq_user_role TO用户；
• 将dbms_aqadm TO用户授予GRANT EXECUTE；
• 在dbms_aq TO用户上执行GRANT EXECUTE；
• 向dbms_aqin TO用户授予GRANT EXECUTE；

desc dbms_aq显示了许多功能，例如DEQUEUE，ENQUEUE，LISTEN

desc dbms_aq shows many functions like DEQUEUE, ENQUEUE, LISTEN

在SQLPlus中同时执行catqueue.sql和dbmsaq.plb

executed in SQLPlus both catqueue.sql and dbmsaq.plb

有人知道什么地方可能出问题吗?

Anybody have idea what can be wrong?

已编辑:

我可以执行以下所有操作: http://rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html ，因此dbms_aq在某种程度上可见，但在我的过程中不可见.

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.

推荐答案

如果您这样做...

SYS.DBMS_AQ 

而不是

DBMS_AQ

如果是，则您缺少同义词.

If so, you're missing a synonym.

编辑:

如果您现在获得的是"PLS-00201:标识符'SYS.DBMS_AQ"，那么我会仔细检查您的赠款.

If you're now getting "PLS-00201: identifier 'SYS.DBMS_AQ" then I'd double check your grants.

GRANT EXECUTE ON SYS.DBMS_AQ to <your-user>;

还要确认一下，您是直接授予用户执行权限 ，而不是通过角色吗?

Also, just to confirm, you've granted the execute privilege directly to the user, and not via a role?

这篇关于Oracle中的PL/SQL函数看不到DBMS_AQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！