PL / SQL function in Oracle cannot see DBMS_AQ
I have a problem with Oracle 9.2 and JMS. I created a PL / SQL procedure to send XML text (from a file or CLOB) to a queue, but this procedure does not compile.
My code looks like (post padding 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 keeps this:
Error(6,21): PLS-00201: identifier 'DBMS_AQ' must be declared
I looked at some newsgroups and tried everything I found with no success.
-
granted (with success) many permissions
- GRANT RESOURCE To the user:
- GRANT CONNECT TO user;
- GRANT TO PERFORM ANY PROCEDURE for the user;
- GRANT aq_administrator_role to the user,
- GRANT aq_user_role to the user,
- GRANT EXECUTE ON dbms_aqadm For user;
- GRANT EXECUTE ON dbms_aq For user;
- GRANT EXECUTE ON dbms_aqin TO user;
-
desc dbms_aq shows a lot of functions like DEQUEUE, ENQUEUE, LISTEN
-
executed in SQLPlus both catqueue.sql and dbmsaq.plb
Anyone have an idea what might be wrong?
EDITED
I can do everything as described in: http://rwijk.blogspot.com/2009/02/whats-in-my-jms-queue.html , so dbms_aq can be seen somehow, but not in my procedure.
Does this work if you ...
SYS.DBMS_AQ
instead
DBMS_AQ
If so, you are missing a synonym.
EDIT
If you are currently receiving "PLS-00201: ID" SYS.DBMS_AQ "I will double check your grants.
GRANT EXECUTE ON SYS.DBMS_AQ to <your-user>;
Also, to confirm, did you grant the execute privilege directly to the user and not through the role?
To use objects in stored procedures, you must have WITH GRANT OPTION grants.
In this case, you GRANT EXECUTE ON SYS.DBMS_AQ WITH POLISH GRANT to;