RE: Advanced queuing : dequeing from remote database

I would suggest you have the enqueue populate to the remote database queue =
and deal with it locally.

Nick

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of John Dunn
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           :=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@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


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
This message is intended solely for the use of the individual or organisati=
on to whom it is addressed.  It may contain privileged or confidential info=
rmation.  If you have received this message in error, please notify the ori=
ginator immediately.  If you are not the intended recipient, you should not=
 use, copy, alter, or disclose the contents of this message.  All informati=
on or opinions expressed in this message and/or any attachments are those o=
f the author and are not necessarily those of VarTecTelecom Europe Ltd or i=
ts affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss=
 or damage arising from its use, including damage from virus.=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D

--
http://www.freelists.org/webpage/oracle-l

Other related posts: