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.
source to share
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?
source to share