How to create a queue user in oracle?

Question

I have a QUEUE_OWNER schema that has some queues. When I connect the application to that data source everything works fine and the app can read the from the queues.

I want to create a _USER schema that has access to the queues so I can connect the app to it and not directly to the _OWNER schema.

This is what I tried:

BEGIN 
    
    FOR Q IN (SELECT * FROM ALL_QUEUES WHERE owner = 'AQ_OWNER') LOOP 
    DBMS_OUTPUT.PUT_LINE('queue = ' ||Q.NAME); 
    
    DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('ALL','AQ_OWNER.'||Q.NAME ,'AQ_USER',FALSE); 
    END LOOP;
    
END; 

but when I put a message in the queue nothing happens in the app.

Answer 1

How about a little help of your DBA?

This is what my user SCOTT sees in all_queues:

SQL> select owner, name from all_queues;

OWNER                          NAME
------------------------------ ------------------------------
SYS                            SRVQUEUE
SYS                            SCHEDULER_FILEWATCHER_Q
SYS                            SCHEDULER$_EVENT_QUEUE

However, I'd like to see some other data. SYS almighty sees it all:

SQL> show user
USER is "SYS"
SQL> select owner, name from dba_queues;

OWNER                          NAME
------------------------------ ------------------------------
SYS                            SYS$SERVICE_METRICS
SYS                            AQ$_SYS$SERVICE_METRICS_TAB_E
SYSTEM                         DEF$_AQERROR
SYSTEM                         AQ$_DEF$_AQERROR_E
SYSTEM                         DEF$_AQCALL
SYSTEM                         AQ$_DEF$_AQCALL_E
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_E
<snip>

Still connected as SYS, I'll create a view which show data only for owner I choose (there's nothing much to choose in my XE database so I'll use SYSTEM-owned values). Then grant select privilege to SCOTT:

SQL> create or replace view v_dba_queues as
  2  select name
  3  from dba_queues
  4  where owner = 'SYSTEM';

View created.

SQL> grant select on v_dba_queues to scott;

Grant succeeded.

Back to SCOTT: to make my life simpler, I'll create a synonym first:

SQL> connect scott/tiger
Connected.
SQL> create synonym v_dba_queues for sys.v_dba_queues;

Synonym created.

Finally:

SQL> select * from v_dba_queues;

NAME
------------------------------
DEF$_AQERROR
AQ$_DEF$_AQERROR_E
DEF$_AQCALL
AQ$_DEF$_AQCALL_E

SQL>

---

Basically, you'd do the same; it's just that your view would contain data for owner = 'QUEUE_OWNER'. See if it helps.