Advance Queuing over a database link

From: <uneboite_at_hotmail.com>
Date: Fri, 11 Jan 2008 13:44:57 -0800 (PST)
Message-ID: <0815710c-2141-488a-8556-8a66c77b1291@l1g2000hsa.googlegroups.com>

Hi!

I'm trying to use AQ propagation but so far never succeeded

I have two databases OMNI_D and OMNI_T, both Oracle 9i There is a working database link between these two When on omni_d, I try:

   select 1 from dual_at_omni_t;
   and it works fine

AQ on omni_d works fine, I've succeeded enqueing and dequeing messages AQ on omni_t works fine, I've succeeded enqueing and dequeing messages But when I try dequeing a message on omni_t that was supposed to be propagated from omni_d it never works
I don't have any error message, it's just that the dequeue never "comes back" from its call, like it never sees any message propagated from omni_d... It waits, until I enqueue a message on omni_t...

The initialization parameter JOB_QUEUE_PROCESSES is set to 2 on both databases

Below are my AQ setup and what I tried...

I'm not sure where I should use the db link in all the strings parameters. Should I use it when I create a subscriber? The recipient list?
Anywhere else? Or just in the destination parameter in the schedule_propagation call? And please, why?

Please any help would be greatly appreciated...

Preparation on database omni_t:
CREATE TYPE queue_message_type AS object(somechar varchar2(10));

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table=>'queue_message_table', multiple_consumers=>true, queue_payload_type=>'queue_message_type');

EXEC DBMS_AQADM.CREATE_QUEUE(queue_name=>'message_queue', queue_table=>'queue_message_table');

EXEC DBMS_AQADM.START_QUEUE(queue_name=>'message_queue');

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('subscriber1', 'message_queue', null);   dbms_aqadm.add_subscriber(queue_name=>'message_queue', subscriber=>subscriber);
end;

Preparation on database omni_d:
CREATE TYPE queue_message_type AS object(somechar varchar2(10));

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table=>'queue_message_table', multiple_consumers=>true, queue_payload_type=>'queue_message_type');

EXEC DBMS_AQADM.CREATE_QUEUE(queue_name=>'message_queue', queue_table=>'queue_message_table');

EXEC DBMS_AQADM.START_QUEUE(queue_name => 'message_queue');

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('subscriber1', 'message_queue', null);   dbms_aqadm.add_subscriber(queue_name=>'message_queue', subscriber=>subscriber);
end;

exec DBMS_AQADM.SCHEDULE_PROPAGATION(queue_name=>'message_queue', destination=>'omni_t', latency=>0);

Enqueue from database omni_d:
DECLARE

    queue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;
    recipients          dbms_aq.aq$_recipient_list_t;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_id          RAW(16);
    my_message          queue_message_type;

BEGIN
    recipients(1) := sys.aq$_agent(NULL, 'message_queue', NULL);     message_properties.recipient_list := recipients;     my_message := queue_message_type('hello');

    DBMS_AQ.ENQUEUE(queue_name=>'message_queue', enqueue_options=>queue_options,
message_properties=>message_properties, payload=>my_message, msgid=>message_id);

    COMMIT;
END;
/

Dequeue on database omni_t:
DECLARE
    msg_handle RAW(16);
    message queue_message_type;
    deq_opt dbms_aq.dequeue_options_t;     msg_p dbms_aq.message_properties_t; BEGIN
    deq_opt.consumer_name := 'subscriber1';     dbms_aq.dequeue(queue_name=>'message_queue', dequeue_options=>deq_opt, message_properties=>msg_p, payload=>message, msgid=>msg_handle);
end;
/
Received on Fri Jan 11 2008 - 15:44:57 CST