Advanced queuing : dequeing from remote database 2005-02-17 - By John Dunn
global_names is set to false on both databases
John
-- --Original Message-- --
From: Maertensj@(protected) [mailto:Maertensj@(protected)]
Sent: 17 February 2005 14:09
To: jdunn@(protected)
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@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of John Dunn
Sent: Thursday, February 17, 2005 7:33 AM
To: Oracle-L@(protected)
Cc: rjamya@(protected); ntilbury@(protected)
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@(protected)
where VAN9 is my database link
but the entry on the queue seems to trying to use
OPD4678.NEW_JOB_MESSAGE_QUEUE@(protected)
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@(protected)]
Sent: 17 February 2005 10:46
To: 'Oracle-L@(protected)'
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@(protected);
*
ERROR at line 6:
ORA-06550 (See ORA-06550.ora-code.com): line 6, column 34:
PLS-00331: illegal reference to OPD4678.NEW_JOB_MESSAGE@(protected)
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@(protected);
--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@(protected)',
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;
/
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
