This looks like the most applicable forum for this. I have set up 2 queues & one topic for the Oracle Advanced Queuing on Oracle 10.2. I've set up the messaging thru WebLogic 11g. This is in schema A. It all works fine when connected as A. However I have some new features to try so I created schema B and "copied" over the tables I need from A. Both A and B have access to the AQ views & packages as documented:
Grant aq_user_role TO B; (and A)
Grant execute ON sys.dbms_aqadm TO B;
Grant execute ON sys.dbms_aq TO B;
Grant execute ON sys.dbms_aqin TO B;
Grant execute ON sys.dbms_aqjms TO B;
When I try the application logged in as B, I get an "insufficient privileges" error in a procedure at the line:
(queue_table => 'A.arf_jms_topic',
purge_condition => 'qtview.queue =''JOBSTATUSCHANGETOPIC'' '
|| 'and qtview.msg_state = ''READY'' '
|| 'and qtview.consumer_name='''
purge_options => po);
I also had A grant all on arf_jms_topic but that didn't help.
Any suggestions would be helpful. Please let me know if I need to explain further.