4

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;

And Oracle raports that:

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. 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;

  2. desc dbms_aq shows many functions like DEQUEUE, ENQUEUE, LISTEN

  3. executed in SQLPlus both catqueue.sql and 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.

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114

3 Answers3

12

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.

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?

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • No. In SQL Developer I added sys. then code completion propose dbms_aq, but after that I can choose between AQ$_DEQUEUE(), ..., UNREGISTER(...), but there is no enqueue_options_t nor message_properties_t. – Michał Niklas Sep 07 '09 at 05:46
  • After I added sys. Oracle changed error message to: Error(6,21): PLS-00201: identifier 'SYS.DBMS_AQ' must be declared – Michał Niklas Sep 07 '09 at 05:50
  • It worked after I granted from sys user not user as sysdba. Thanks! – Michał Niklas Sep 07 '09 at 10:42
0

I had the same error. If you are sure that DBMS_AQ exist, make sure you give grant with SYS user not SYSTEM. Because SYS owns the data dictionary, it's considered a bit more special than SYSTEM.

Community
  • 1
  • 1
Elvis23
  • 1
  • 4
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34896547) – Yaroslavm Aug 30 '23 at 00:07
-1

In order to use objects in stored procedures, you must have grants WITH GRANT OPTION.

In this case you smust have GRANT EXECUTE ON SYS.DBMS_AQ WITH GRANT OPTION to ;

Vladimir
  • 19
  • 1