Home » SQL & PL/SQL » SQL & PL/SQL » DBLINK, DBMS_AQADM, STOP/START QUEUE (Oracle Database 11g Enterprise Edition Release - 64bit Production)
DBLINK, DBMS_AQADM, STOP/START QUEUE [message #584684] Thu, 16 May 2013 14:47 Go to next message
Messages: 2
Registered: May 2013
Junior Member
We would like to manage AQs across numerous databases from a central "parent" via DBLINKS.

Specifically we want to programmatically stop and restart dequeuing where the only knowns are the database and schema names. Via DBLINKs the program already determines and persists the available queues and their starting states> What we also need it to do is to either disable (as required) or re-enable back to the original start points. The ability to perform the latter operations over DBLINKs is what I need some assistance with.

What we don't want to do (if possible) is to build and maintain stored procedures/functions all over the place to provide functionality that should be "generalizable" on a central - "parental" - node.

So, we've established links to the "child" databases using a common username, granting that user the privileges required to execute the DBMS_AQADM.STOP_QUEUE and DBMS_AQADM.START_QUEUE commands. I've able to successfully issue selects, etc. from the parent to the children via the links.

Where I'm stumped is in determining the correct syntax for parental performance of DBMS_AQADM operations on the children.

For example, both:

BEGIN DBMS_AQADM.STOP_QUEUE( 'SCHEMA.QNAME@target.system.cornell.edu', enqueue=>FALSE, dequeue=>TRUE, wait=>TRUE ); END;


BEGIN DBMS_AQADM.START_QUEUE( 'SCHEMA.QNAME@target.system.cornell.edu', enqueue=>FALSE, dequeue=>TRUE ); END;

result in:

ORA-24000: invalid value SCHEMA.QNAME@target.system.cornell.edu, QUEUE_NAME should be of the form [SCHEMA.]NAME
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4792
ORA-06512: at "SYS.DBMS_AQADM", line 217
ORA-06512: at line 13

I've tried many, many, MANY combinations of the pertinent values and am stuck.

Please advise as to what the correct syntax for this type of operation over a DBLINK should be.

Thanks much in advance.
Re: DBLINK, DBMS_AQADM, STOP/START QUEUE [message #584685 is a reply to message #584684] Thu, 16 May 2013 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BEGIN DBMS_AQADM.STOP_QUEUE@target.system.cornell.edu( 'SCHEMA.QNAME...

Re: DBLINK, DBMS_AQADM, STOP/START QUEUE [message #584686 is a reply to message #584685] Thu, 16 May 2013 14:54 Go to previous message
Messages: 2
Registered: May 2013
Junior Member
Wow, that was simple. Thanks VERY much Michel!!!!
Previous Topic: Please provide real time scenario for when we use object type in PL/SQL block
Next Topic: REPLACE not replacing
Goto Forum:

Current Time: Sat Aug 02 00:19:41 CDT 2014

Total time taken to generate the page: 0.09286 seconds