global_names is set to false on both databases John -----Original Message----- From: Maertensj@xxxxxxxxxxxxx [mailto:Maertensj@xxxxxxxxxxxxx] Sent: 17 February 2005 14:09 To: jdunn@xxxxxxxxx Subject: RE: Advanced queuing : dequeing from remote database You've probably got GLOBAL_NAMES set to TRUE. If you do, and don't specify a domain when you create a link, your link name will automatically append the database's default domain on to the end. Do a select * from global_name to see what your database global name is set to. Also, do a select db_link from dba_db_links to see what Oracle is actually using for your link name. The only workarounds are to set global_names to false, or recreate your link as VAN9.SOMEDOMAIN.COM and change the corresponding tns entry. GLOBAL_NAMES is a bit of a pain to deal with, but many companies like it for security reasons. Hope this helps... Joey Maertens -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Dunn Sent: Thursday, February 17, 2005 7:33 AM To: Oracle-L@xxxxxxxxxxxxx Cc: rjamya@xxxxxxxxx; ntilbury@xxxxxxxxxxxx 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@VAN9 where VAN9 is my database link but the entry on the queue seems to trying to use OPD4678.NEW_JOB_MESSAGE_QUEUE@xxxxxxxxxxxxxxxxxx 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 [mailto:jdunn@xxxxxxxxx] Sent: 17 February 2005 10:46 To: 'Oracle-L@xxxxxxxxxxxxx' 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@DEMO; * ERROR at line 6: ORA-06550: line 6, column 34: PLS-00331: illegal reference to OPD4678.NEW_JOB_MESSAGE@DEMO Any ideas what I have done wrong? John ---------------------------------------------------------------------------- ---------- set serveroutput on declare dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; message_handle RAW(16); message opd4678.new_job_message@DEMO; --message opd4678.new_job_message; BEGIN 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'; DBMS_AQ.DEQUEUE ( queue_name => 'opd4678.new_job_message_queue@DEMO', dequeue_options => dequeue_options, message_properties => message_properties, payload => message, msgid => message_handle ); dbms_output.put_line('+-----------------+'); dbms_output.put_line('| New Job |'); dbms_output.put_line('+-----------------+'); dbms_output.put_line('- Message ID := ' || message.message_id); dbms_output.put_line('- Filename := ' || message.host_file_name); COMMIT; -- ------------------------------------------------------- END; / -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l