1 Reply Latest reply: Sep 7, 2011 7:30 AM by chrdei RSS

    Message propagation to dynamic recipients

    chrdei
      Hi,

      I have a new problem with the Oracle Advanced Queuing feature.
      The use case looks like:
      One central database which propagates messages to a unknown number of subscribers, let it call satellites. Every message is only addressed to one satellite. Up to now this will not change. The the message is addressed during runtime.

      Create script for the queue (CENTRAL):
      BEGIN
        -- Create Queue Table
        dbms_aqadm.create_queue_table(
          queue_table         =>  'QT_TASK_C',
          queue_payload_type  =>  'sys.XmlType',
          multiple_consumers  =>  true
        );
      
        -- Create Queue
        dbms_aqadm.create_queue(
          queue_name  =>  'Q_TASK_C',
          queue_table =>  'QT_TASK_C'
        );
        
        -- Start Queue
        dbms_aqadm.start_queue(
          queue_name  => 'Q_TASK_C'
        );   
      END;
      /
      
      BEGIN
        dbms_aqadm.remove_subscriber(
          queue_name      =>  'Q_TASK_C',,
          subscriber      =>  sys.aq$_agent('RECIPIENT', 'Q_TASK_P@DBLINK', 0)
          -- queue_to_queue  =>  true
        );
      END;
      Create script for the queue (SATELLITES):
      BEGIN
        -- Create Queue Table
        dbms_aqadm.create_queue_table(
          queue_table         =>  'QT_TASK_P',
          queue_payload_type  =>  'sys.XmlType',
          multiple_consumers  =>  true
        );
      
        -- Create Queue
        dbms_aqadm.create_queue(
          queue_name  =>  'Q_TASK_P',
          queue_table =>  'QT_TASK_P'
        );
        
        -- Start Queue
        dbms_aqadm.start_queue(
          queue_name  =>  'Q_TASK_P'
        );
      END;
      I don't add subscribers with the aq api and I don't add a propagation scheduler. Is this okay?

      This is the code for the enqueue process.
      DECLARE  
        arrRecipients       dbms_aq.aq$_recipient_list_t;
        
        recEnqueueOptions   dbms_aq.enqueue_options_t;
        recMsgOptions       dbms_aq.message_properties_t;
        handle              RAW(16);
        
        objTask             obj_task;
      BEGIN    
        -- For the payload
        objTask :=  obj_transport_task(
                      i_nTaskId         =>  1,
                      i_sTargetSystem   =>  'X123VB [DBLINK1]',
                      i_sDbLink         =>  'DBLINK1'
                    );
      
        recEnqueueOptions.visibility  :=  dbms_aq.on_commit;
        
        arrRecipients(1)  :=  sys.aq$_agent( 'RECIPIENT', 'SCOTT.Q_TASK_P@DBLINK1', 0 );
        recMsgOptions.recipient_list  := arrRecipients;
        
        dbms_aq.enqueue(
          queue_name          =>  'SCOTT.Q_TASK_C',
          enqueue_options     =>  recEnqueueOptions,
          message_properties  =>  recMsgOptions,
          payload             =>  objTask.toXml(),
          msgid               =>  handle
        );
      
        COMMIT;
      END;
      Unfortunately the messages will not be propagated. Why? The database link works and if I store the subscribers into the configuration every subscriber gets the messages, but this is not the use case.

      Can you help me with this problem?