Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Advanced queuing : dequeing from remote database

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Fri, 18 Feb 2005 06:51:18 -0600
Message-ID: <83FCA77436D6A14883E132C63F4101D0028BF94D@pscdalpexch50.perotsystems.net>


=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                            FAILURES

------------------------------ ----------
LAST_ERROR_MSG


NEW_JOB_MESSAGE_QUEUE                   5
ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_AQADM' must be declared ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 -----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;
                                 *

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?

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-l
Received on Fri Feb 18 2005 - 07:55:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US