Task 4: Configuring a Messaging Client to Dequeue Messages

Configure a mechanism to dequeue the messages in your messaging system. Typically, an application dequeues and processes messages that were created by another application. For simplicity, this example creates a PL/SQL procedure called dequeue_orders to dequeue messages that include the order ID and order date of an order. You call the procedure to dequeue messages in this example, but an application can run such a procedure periodically.

To configure a messaging client to dequeue messages: 

1. Grant EXECUTE privilege on the SYS.DBMS_STREAMS_MESSAGING package to the Oracle Streams administrator at the ii2.example.com database. See Step 1 in "Task 3: Configuring a Message Enqueuing Mechanism" for an example that grants this privilege to the Oracle Streams administrator in the ii1.example.com database.

2. On a command line, open SQL*Plus and connect to the ii2.example.com database as the Oracle Streams administrator.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

3. Create a messaging client to enable the Oracle Streams administrator to dequeue messages from the streams_queue queue:

   BEGIN
     DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
       message_type   => 'strmadmin.order_id_date',
       rule_condition => ':MSG.ORDER_ID > 0',
       streams_type   => 'DEQUEUE',
       streams_name   => 'strmadmin',
       queue_name     => 'strmadmin.streams_queue');
   END;
   /
   

   The user name of the Oracle Streams administrator must be specified for the streams_name parameter. In this example, the user name of the Oracle Streams administrator is strmadmin.

   A messaging client uses rules to determine which messages to dequeue. In this example, the rule for the messaging client specifies that all messages with an order_id greater than zero should be dequeued. So, with this rule, the messaging client will dequeue all new messages of type strmadmin.order_id_date that appear in the strmadmin.streams_queue queue.

4. In Oracle Enterprise Manager, log in to the ii2.example.com database as the Oracle Streams administrator.

5. Go to the Database Home page.

6. Click Schema to open the Schema subpage.

7. Click Procedures in the Programs section.

8. On the Procedures page, click Create.

9. On the Create Procedure page, enter dequeue_orders in the Name field.

10. Ensure that strmadmin is entered in the Schema field.

11. Delete the sample text in the Source field.

12. Enter the following in the Source field:

    AS 
      msg            ANYDATA; 
      user_msg       strmadmin.order_id_date; 
      num_var        PLS_INTEGER; 
      more_messages  BOOLEAN := TRUE; 
      navigation     VARCHAR2(30); 
    BEGIN 
      navigation := 'FIRST MESSAGE'; 
      WHILE (more_messages) LOOP 
        BEGIN 
          DBMS_STREAMS_MESSAGING.DEQUEUE( 
            queue_name   => 'strmadmin.streams_queue', 
            streams_name => 'strmadmin', 
            payload      => msg, 
            navigation   => navigation, 
            wait         => DBMS_STREAMS_MESSAGING.NO_WAIT); 
          IF msg.GETTYPENAME() = 'STRMADMIN.ORDER_ID_DATE' THEN 
            num_var := msg.GETOBJECT(user_msg); 
            DBMS_OUTPUT.PUT_LINE('Order ID: ' || user_msg.order_id); 
            DBMS_OUTPUT.PUT_LINE('Order Date: ' || user_msg.order_date); 
          END IF; 
          navigation := 'NEXT MESSAGE'; 
          COMMIT; 
        EXCEPTION WHEN SYS.DBMS_STREAMS_MESSAGING.ENDOFCURTRANS THEN 
                    navigation := 'NEXT TRANSACTION'; 
                  WHEN DBMS_STREAMS_MESSAGING.NOMOREMSGS THEN 
                    more_messages := FALSE; 
                    DBMS_OUTPUT.PUT_LINE('No more messages.'); 
                  WHEN OTHERS THEN 
                    RAISE;   
        END; 
      END LOOP; 
    END;
    

13. Click OK.

14. Complete the steps in "Task 5: Enqueuing Messages" to continue this extended example.