1. Read the manual. Set up the grants and stuff as directed. Note that the following assumes that you are connected to the database as finagle.

2. Create an old-style database link with the username and password in it. If you don’t AQ will try to log on to the remote machine as sys and give you an ORA-04052: error occurred when looking up remote object … error if you use the one where you just give the connect string. Some kind of security thing, the manual for the error message says you need some views installing. Ignore it and create one of these:

create public database link yada_remote
connect to finagle identified by frippery
using ‘yada.world’

(this cost me 2 days)

3. create your payload type on both instances say my_payload

create or replace type my_payload is object
( id   integer
, XML                CLOB
);

4. create queue table and queue on local instance and start it (I put this in a package to save typing and make it portable)

 dbms_aqadm.create_queue_table
  ( queue_table        => ‘marvy_queue_tab’
  , comment            => ‘My marvy queue’
  , multiple_consumers => TRUE
  , queue_payload_type => ‘my_payload’
  );
 dbms_aqadm.create_queue
  ( queue_name   => ‘finagle.marvy_queue’
  , queue_table  => ‘marvy_queue_tab’
  );
  dbms_aqadm.start_queue ( ‘finagle.marvy_queue’ ) ;

5. create queue on remote instance and start it

<same commands as before>

6. add remote subscriber on the local instance. The @link tells AQ that there is a subscriber at the remote instance. Do this on the local instance.

DECLARE
  subscriber         sys.aq$agent;
BEGIN
  subscriber := sys.aq$agent(null,‘finagle.marvy_queue@yada_remote’,null);
  dbms_aqadm.add_subscriber(
   queuename         => ’finagle.marvyqueue’,
   subscriber         => subscriber,
   rule           => null,
   transformation => null );
END;

7. start propagation for local and remote destinations on source instance

EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(-
   Queue_name    =>    ‘finagle.marvy_queue’ )

EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(-
   Queue_name    =>    ‘finagle.marvy_queue’, –
   Destinatio   =>    ‘yada_remote’);

This will start pushing data to the remote queue. Note that <schema>.<queue> have to be the same.

8. Test it!

Send a test message:

— send an empty object
declare
thing my_payload ;
dummy raw(16) ;
begin
thing := new my_payload( 1, null ) ;
  DBMS_AQ.enqueue
  ( queue_name             => ’finagle.marvy_queue
  , enqueue_options        => <read the manual>
  , message_properties     => <read the manual>
  , payload                => thing
  , msgid                  => dummy
  );
end ;

— remote dequeue
declare
pi_dequeue_options        DBMS_AQ.dequeue_options_t  := <some clever package>.DEFAULT_DEQUEUE_OPTIONS ;
po_message_properties     DBMS_AQ.message_properties_t ;
po_msgid                  RAW ;
thing               my_payload ;
BEGIN
  pi_dequeue_options.consumer_name := ‘REMOTE_SUB’;

  DBMS_AQ.dequeue( queue_name             => ‘finagle.marvy_queue’ 
                 , dequeue_options        => pi_dequeue_options
                 , message_properties     => po_message_properties
                 , payload                => thing
                 , msgid                  => po_msgid
                 );
dbms_output.put_line( thing.id ) ;
END ;

If aq just isn’t working there’s a useful (but a little out of date) fault finder on metalink: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=233099.1.

I’m pretty curious about queues being used bi-directionally. I can’t see how it could stop you if you set propagation up on both instances. I may need this, but would have to then send messages to a specific subscriber or you’d be sending messages back to the same instance, maybe you don’t care.