| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advanced queuing : dequeing from remote database
=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Dunn
Sent: Friday, February 18, 2005 6:49 AM
To: Oracle-L_at_freelists.org
Cc: rjamya_at_gmail.com; ntilbury_at_vartec.co.uk
Subject: RE: Advanced queuing : dequeing from remote database
Ok, I'm getting further now. I got round the identifier 'DBMS_AQADM'
must be
declared error by explicitly granting execute on the package, rather
than
thru the role.
Now I get the error :
ORA-04052: error occurred when looking up remote object OPD4678.DBMS_AQADM_at_VAN9.SUN1
ORA-00604: error occurred at recursive SQL level 4 ORA-01005: null password given; logon denied ORA-06512: at "SYS.DBMS_AQADM_SYS", line 977
My database link is a private one. Is this the problem? Do I need to use
a
public database link with AQ, with a connect string?
-----Original Message-----
From: John Dunn [mailto:jdunn_at_sefas.com] Sent: 17 February 2005 16:53 To: 'Oracle-L_at_freelists.org' Cc: 'rjamya_at_gmail.com'; 'ntilbury_at_vartec.co.uk' Subject: RE: Advanced queuing : dequeing from remote database
I have overcome the connection description for remote database not found
error but now get the following error : Looks like a privilage thing,
but I
do not understand this. Since I am able to run dbms_aqadm commands under
my
user on both databases surely the privileges are OK?
SQL> select qname,failures,last_error_msg from dba_queue_schedules;
QNAME FAILURESLAST_ERROR_MSG
------------------------------ ----------
NEW_JOB_MESSAGE_QUEUE 5 ORA-06550: line 1, column 7:
-----Original Message-----
From: John Dunn [mailto:jdunn_at_sefas.com] Sent: 17 February 2005 13:33 To: 'Oracle-L_at_freelists.org' Cc: 'rjamya_at_gmail.com'; 'ntilbury_at_vartec.co.uk' 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 [mailto:jdunn_at_sefas.com] Sent: 17 February 2005 10:46 To: 'Oracle-L_at_freelists.org' 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;
*
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_at_DEMO;
--message opd4678.new_job_message;
BEGIN
dequeue_options.CONSUMER_NAME :=3D NULL;
dequeue_options.DEQUEUE_MODE :=3D DBMS_AQ.REMOVE;
dequeue_options.NAVIGATION :=3D DBMS_AQ.NEXT_MESSAGE;
dequeue_options.VISIBILITY :=3D DBMS_AQ.IMMEDIATE;
dequeue_options.WAIT :=3D DBMS_AQ.FOREVER;
dequeue_options.MSGID :=3D null;
dequeue_options.CORRELATION :=3D 'TEST MESSAGE';
DBMS_AQ.DEQUEUE (
queue_name =3D> 'opd4678.new_job_message_queue_at_DEMO',
dequeue_options =3D> dequeue_options,
message_properties =3D> message_properties,
payload =3D> message,
msgid =3D> message_handle
);
dbms_output.put_line('+-----------------+');
dbms_output.put_line('| New Job |');
dbms_output.put_line('+-----------------+');
dbms_output.put_line('- Message ID :=3D ' || message.message_id);
dbms_output.put_line('- Filename :=3D ' ||
message.host_file_name);
COMMIT;
END;
/
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 18 2005 - 07:55:51 CST
 |
 |