I am using oracle 11.2.
Database users:
I have following stored procedure, which compiled OK. and it is invoked from trigger on table.
CREATE OR REPLACE PROCEDURE scott.PROC_JMS_ENQUEUE (NAME VARCHAR,
MESSAGE IN VARCHAR)
AS
msg SYS.AQ$_JMS_TEXT_MESSAGE;
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
msg_id RAW (16);
no_consumers_error EXCEPTION;
PRAGMA EXCEPTION_INIT (no_consumers_error, -24033);
BEGIN
-- Ensure what is sent will be a JMS message
msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT ();
msg.set_text (MESSAGE);
--ENQUEUE
DBMS_AQ.ENQUEUE (queue_name => 'aqadm.my_queue',
enqueue_options => queue_options,
message_properties => msg_props,
payload => msg,
msgid => msg_id);
-- Without the following, the procedure will die if none
-- Is listening for cache control
EXCEPTION
WHEN no_consumers_error
THEN
-- Output it in case, but otherwise swallow
DBMS_OUTPUT.PUT_LINE (
'No interested parties are listening to messages');
END;
/
I have following GRANT to user
GRANT EXECUTE ON AQ_USER_ROLE TO scott;
GRANT EXECUTE ON SYS.DBMS_AQ TO scott;
GRANT EXECUTE ON SYS.DBMS_AQIN TO scott;
--GRANT EXECUTE ON SYS.DBMS_AQJMS_INTERNAL TO scott;
--GRANT EXECUTE ON SYS.DBMS_TRANSFORM TO scott;
GRANT EXECUTE ANY PROCEDURE TO scott;
When i execute above stored procedure, or update table that fires trigger code, i get following error:
exec PROC_JMS_ENQUEUE('Test Message','Dam');
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_AQ", line 169
ORA-06512: at "SCOTT.PROC_JMS_ENQUEUE", line 19
ORA-06512: at line 1
EDIT: Here is privileges view, all are granted as SYS as SYSDBA
It worked when i grant following
GRANT ENQUEUE ANY QUEUE TO SCOTT;
Oracle sucks on one consolidated documentation!
Also, some reason GRANT has to be "direct" not via "role" in order to execute via PL/SQL Trigger or Procedure!!!!!