Can you dequeue over a DBLink?

Question

I'm trying to dequeue from a queue on another server. I can't use propagation to get it to the server that needs the data. If I put a dblink in the name of the queue in the dequeue options I get an error.

Error at line 9
ORA-25200: invalid value prim_queues.prim_trade_q@prim_dbln, QUEUE_NAME should be [SCHEMA.]NAME
ORA-06512: at "SYS.DBMS_AQ", line 619
ORA-06512: at "PSINET_AQ.AQ_INBOUND_TRADES_PKG", line 20
ORA-06512: at line 10

So I'm guessing that's not possible.

Instead I'll put the DEQUEUE in a package on the server WITH the queue and just call that package across the link.

Is this right?

EDIT:

Igor mentions using dynamic SQL but I put a package on the server with the queue. That too is parsed on the same side. The problem then becomes the dequeued object exists only on that queue's server. Even if we have an identical one on the opposite side.

Answer 1

You can use a DB Link to call the remote version of DBMS_SQL for dynamic SQL (or anonymous PL/SQL block), which means the statement is parsed at the remote database but as a 'local' statement.

begin dbms_sql.parse@dblink(...); end;