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.
granted (with success) many permisions
desc dbms_aq shows many functions like DEQUEUE, ENQUEUE, LISTEN
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.
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?