Querying OracleAQ views from a package in a different schema

Question

I have an OracleAQ multiconsumer queue owned by schema A. As explained in the Oracle doc, there are some views (schema.aq$table_name*) to check the status of the queue table.

I can query these views from another schema through plain SQL or PL/SQL code, but not if this code is wrapped inside a package. The package body refuses to compile with a ORA-00942: table or view does not exist

The second schema has all the necessary rights since I can actually query the view from outside the package.

Answer 1

I performed the following successfully:

1. Created AQ Table (called MY_Q) in Schema A
2. Granted "Select" privs to Schema B to the view AQ$MY_Q
3. Created package (called Q_PKG) in Schema B that selects records from AQ$MY_Q
4. Executed Q_PKG in Schema B

Not sure (without seeing your Schema information) whether there is another priv higher in priority that is restricting your AQ View from being read. This stackoverflow post shows some information on "displaying" a user's privs.