Advanced queuing : dequeing from remote database

From: John Dunn <jdunn_at_sefas.com>
Date: Thu, 17 Feb 2005 10:45:39 -0000
Message-ID: <000001c514dd$d3033d60$3e04050a@johnman>

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           := 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_at_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;
/

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

Received on Thu Feb 17 2005 - 05:46:37 CST