Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advanced queuing : dequeing from remote database

RE: Advanced queuing : dequeing from remote database

From: Powell, Mark D <>
Date: Thu, 17 Feb 2005 09:12:40 -0500
Message-ID: <>

>From the 9.2 DBA Admin Guide >> To understand how a database link works, you
must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. Oracle forms a database's global database name by prefixing the database's network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter <<

If you do not specify the domain name in the link then Oracle attempts to provide it. Redefine the link as database_name.domain to avoid problems like this. Remember that in the old days Oracle defaulted the domain name to world. Now that is not true any longer so you should always specify it.

HTH -- Mark D Powell --

-----Original Message-----
[]On Behalf Of John Dunn Sent: Thursday, February 17, 2005 8:33 AM To:
Cc:; Subject: RE: Advanced queuing : dequeing from remote database

I have identified the error as :

connection description for remote database not found

The address being used in add_subscriber is OPD4678.NEW_JOB_MESSAGE_QUEUE_at_VAN9 where VAN9 is my database link

but the entry on the queue seems to trying to use

OPD4678.NEW_JOB_MESSAGE_QUEUE_at_VAN9.US.ORACLE.COM Is this the problem?

Why is it trying to use VAN9.US.ORACLE.COM ???

The entry in tnsnames.ora is simply named VAN9.

From: 	John Dunn []
Sent:	17 February 2005 10:46
To:	''
Subject:	Advanced queuing : dequeing from remote database

I am experimenting with advanced queueing in Oracle 9i and am able to enqueue and dequeue messages within the same database.

However I would like to dequeue messages on a remote database via a database link.

I am confused as to what I need to set up on the remote server in order to dequeue the message.

I have set up the database link that points back to the enqueueing database, The database link works OK.

I am then trying to dequeue the message on the remote database using the following code(which is the same I use when doing this all on the same database except for the references to the database link). I get the error :

    message              opd4678.new_job_message_at_DEMO;

ERROR at line 6:
ORA-06550: line 6, column 34:
PLS-00331: illegal reference to OPD4678.NEW_JOB_MESSAGE_at_DEMO

Any ideas what I have done wrong?


    set serveroutput on


    dequeue_options      dbms_aq.dequeue_options_t;
    message_properties   dbms_aq.message_properties_t;
    message_handle       RAW(16);
    message              opd4678.new_job_message_at_DEMO;
    --message              opd4678.new_job_message;


    dequeue_options.CONSUMER_NAME           := NULL;
    dequeue_options.DEQUEUE_MODE            := DBMS_AQ.REMOVE;
    dequeue_options.NAVIGATION              := DBMS_AQ.NEXT_MESSAGE;
    dequeue_options.VISIBILITY              := DBMS_AQ.IMMEDIATE;
    dequeue_options.WAIT                    := DBMS_AQ.FOREVER;
    dequeue_options.MSGID                   := null;
    dequeue_options.CORRELATION             := 'TEST MESSAGE';

       queue_name         => 'opd4678.new_job_message_queue_at_DEMO',
       dequeue_options    => dequeue_options,
       message_properties => message_properties,
       payload            => message,
       msgid              => message_handle
    dbms_output.put_line('| New Job |');
    dbms_output.put_line('- Message ID   := ' || message.message_id);
    dbms_output.put_line('- Filename       := ' || message.host_file_name);



Received on Thu Feb 17 2005 - 09:15:57 CST

Original text of this message