9 Sending Messages Using Oracle Streams Advanced Queuing

About Message Ordering

Message ordering determines the order in which messages are dequeued. Oracle Streams Advanced Queuing (AQ) provides the following options for message ordering:

• Enqueue time: Messages are dequeued in the same order that they were enqueued. This option is sometimes called first in first out (FIFO) ordering. For example, banking applications often use this ordering so that financial transactions are always ordered according to the time when they occurred.

• Priority: A priority is specified for messages during enqueuing, and messages are dequeued based on their priority. For example, shipping companies might use this ordering for messages because some packages have higher priority than others.

• Commit time: Messages are dequeued based on when the transaction that enqueued the messages committed. Commit-time ordering is typically used when the messages result in database changes with transaction dependencies. For example, an application that handles sales for a company might use commit-time ordering when the messages result in changes to several database tables that have dependencies.

About Message Modes

Oracle Streams Advanced Queuing (AQ) supports the following message modes:

• Persistent messaging: Messages are always stored on disk in a database table called a queue table. This type of storage is sometimes called persistent queue storage.

• Buffered messaging: Messages are stored in memory but can spill to a queue table under certain conditions. This type of storage is sometimes called buffered queue storage.

Buffered messaging provides better performance, but it does not support some messaging features, such as message retention. Message retention lets you specify the amount of time a message is retained in the queue table after being dequeued. In addition, when a database goes down unexpectedly, persistent messages are retained on disk, but buffered messages can be lost because they are stored in memory.

Message retention provides an audit trail, which might be required for some organizations. For example, while messages are stored in the queue table, users and applications can run queries to gather information about the messages. For example, a sales application might gather this information to generate a report on orders.

About Message Notifications

Oracle Streams Advanced Queuing (AQ) can notify an application and users when a message of interest is enqueued. Notifications enable applications and users to do their work without constantly checking a queue for new messages. Notifications can be sent to an application using PL/SQL, Java Message Service (JMS), or Oracle Call Interface (OCI) callback functions. Notification can also be sent to a specified e-mail address or HTTP post. In addition, notifications can be presented in either RAW data type form or XML form. Applications and users do not need to be connected to a database to receive notifications. When they are notified that a message of interest has appeared in a queue, they can connect to the database and check the queue.

About Propagations

Propagation is the process of sending messages from one queue to another. These queues can be in the same database or in different databases. Propagations are not a required part of a messaging system. Propagations enable applications to communicate with each other even if they are not connected to the same database or same queue. Also, using multiple propagations, you can send the same messages from one queue to several other queues.

Propagations use database links to send messages between queues at different databases. A database link is a pointer that defines a one-way communication path from an Oracle database to another database. Propagation is performed by an Oracle Scheduler job called a propagation job. A propagation schedule determines how often a propagation sends messages, and you control the schedule for each propagation job.

Oracle Streams Advanced Queuing (AQ) supports both queue-to-queue or queue-to-database link (queue-to-dblink) propagations. A queue-to-queue propagation always has its own exclusive propagation job to send messages from the source queue to the destination queue. Therefore, the propagation schedule of each queue-to-queue propagation can be managed separately. A single database link can be used by multiple queue-to-queue propagations. Use queue-to-queue propagation when you want fine-grained control over multiple propagations that send messages from a single queue, and each propagation should use a different schedule.

In contrast, a queue-to-dblink propagation shares a propagation job with other queue-to-dblink propagations from the same source queue that use the same database link. If several propagations send messages from a single source queue to several queues at the remote database, then these propagations share the same propagation schedule. Any change to the propagation schedule affects all of the queue-to-dblink propagations from the same source queue that use the database link. Use queue-to-dblink propagation when you want bulk control over multiple propagations that send messages from a single queue, and all of the propagations should use the same schedule.

About Oracle Messaging Gateway

Oracle Messaging Gateway integrates an Oracle Streams Advanced Queuing (AQ) messaging system with other messaging systems, such as IBM Websphere MQ (formerly called MQSeries) and TIBCO Rendezvous. This integration enables Oracle Database applications that use Oracle Streams AQ to communicate bidirectionally and seamlessly with other applications that use non-Oracle messaging systems.

Messaging Gateway uses a gateway agent to send messages from Oracle Streams AQ queues to queues in non-Oracle messaging systems. The gateway agent also enables Oracle Streams AQ queues to receive messages from non-Oracle messaging systems.

Messaging Gateway supports automatic type conversion for Oracle Streams AQ types that can be directly mapped to a non-Oracle messaging system. For types that cannot be directly mapped, you can define custom transformations to map the types. Once defined, custom transformations are performed automatically during propagation.

Messaging Gateway supports the native message formats of the messaging systems. Oracle Streams AQ messages can have RAW or any Oracle object type payload supported by Oracle Streams AQ. IBM Websphere MQ messages can be text or byte messages. TIBCO Rendezvous messages can be any TIBCO Rendezvous wire format data type.

Oracle Streams AQ queues can be securely accessed through the Internet. Messaging Gateway provides secure, Internet-enabled messaging between Oracle Streams AQ queues and queues in a non-Oracle messaging system.

Task 1: Creating the Message Type at Each Database

Create a user-defined type to define the information that you want to send about orders. This example creates the strmadmin.order_id_date type for messages that will contain the order ID and order date.

To create the strmadmin.order_id_date type at each database: 

1. Prepare your environment for messaging if you have not already done so. See "Preparing for Messaging".

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

3. Go to the Database Home page.

4. Click Schema to open the Schema subpage.

5. Click Object Types in the User Defined Types section.

6. On the Object Types page, click Create to open the Create Object Type page.

   
   Description of the illustration tdpii_create_object_type.gif
   
   

7. Enter order_id_date in the Name field.

8. Ensure that strmadmin is selected in the Schema field.

9. Ensure that Predefined Type is selected for Datatype in the Attributes section.

10. Click Add in the Attributes section to open the Add Predefined Type Attributes page.

    
    Description of the illustration tdpii_add_type_attribute.gif
    
    

11. To add an attribute:

    1. Enter order_id in the Name field.

    2. Select NUMBER for Type.

    3. Enter 12 in the Length field.

    4. Click OK.

12. On the Create Object Type page, ensure that Predefined Type is selected in the Attributes section.

13. Click Add in the Attributes section to open the Add Predefined Type Attributes page.

14. To add an attribute:

    1. Enter order_date in the Name field.

    2. Select VARCHAR2 for Type.

    3. Enter 100 in the Length field.

    4. Click OK.

15. On the Create Object Type page, click OK to create the type.

16. Log in to the ii2.example.com database as the Oracle Streams administrator in Enterprise Manager.

17. Complete Steps 3 through 15 at the ii2.example.com database so that both databases have the strmadmin.order_id_date type.

18. Complete the steps in "Task 2: Configuring the Queues and Propagation Between Them" to continue this extended example.

Task 2: Configuring the Queues and Propagation Between Them

Create a queue at each database, and create a propagation to send messages about orders from the queue at the ii1.example.com database to the queue at the ii2.example.com database.

To create a propagation: 

1. Create a queue named streams_queue in the schema of the Oracle Streams administrator at both the ii1.example.com and ii2.example.com databases. See "Creating an ANYDATA Queue" for instructions.

2. Create a database link named ii2.example.com in the Oracle Streams administrator schema at ii1.example.com. Configure the database link to connect to the Oracle Streams administrator schema at ii2.example.com. The service name of the database link must be ii2.example.com. See "Tutorial: Creating a Database Link" for instructions.

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

4. Go to the Database Home page.

5. Under High Availability, click the number link in Streams Components.

6. On the Manage Replication page, click Streams to open the Streams subpage.

7. Click Setup Propagation to open the Setup Propagation page.

   
   Description of the illustration tdpii_setup_propgation.gif
   
   

8. Enter send_orders in the Propagation Name field.

9. Enter strmadmin.streams_queue in the Source Queue field. This queue is the queue at the ii1.example.com database into which messages about orders will be enqueued.

10. Enter the name of the database link that you created in Step 2 in the Destination Database Link field. In this example, the database link name is ii2.example.com.

11. Enter strmadmin.streams_queue in the Destination Queue field. This is the queue at the ii2.example.com database to which messages about orders will be sent.

12. Leave the Positive Rule Set and Negative Rule Set fields empty. When a propagation does not have a rule set, it sends all of the messages in the source queue to the destination queue.

13. Enable the Queue to Queue Propagation option.

14. Click OK to create the propagation.

15. Complete the steps in "Task 3: Configuring a Message Enqueuing Mechanism" to continue this extended example.

Task 3: Configuring a Message Enqueuing Mechanism

Configure a mechanism to enqueue the messages in your messaging system. Typically, an application creates and enqueues messages that will be dequeued and processed by another application. For simplicity, this example creates a trigger called enqueue_orders to enqueue a message that includes the order ID and order date of an order. The trigger fires when an order is inserted into the oe.orders table.

To configure a message enqueuing mechanism: 

1. Grant the EXECUTE privilege on the DBMS_STREAMS_MESSAGING package to the Oracle Streams administrator.

   This example configures a trigger that runs the ENQUEUE procedure in the DBMS_STREAMS_MESSAGING package. The user who runs this procedure in a trigger must have explicit EXECUTE privilege on the package that contains the procedure. The privilege cannot be granted through a role. Therefore, the Oracle Streams administrator must be granted explicit EXECUTE privilege on the package.

   1. Log in to Enterprise Manager as an administrative user who can grant privileges to the strmadmin user. For example, you can log in as a user with SYSDBA privilege. In this example, log in to the ii1.example.com database.

   2. Go to the Database Home page.

   3. Click Server to open the Server subpage.

   4. Click Users in the Security section.

      The Users page appears.

   5. Select the STRMADMIN user.

   6. Click Edit.

      The Edit User page appears, showing the General subpage.

   7. Click Object Privileges to open the Object Privileges subpage.

      
      Description of the illustration tdpii_edit_user.gif
      
      

   8. Select Package in the Select Object Type list.

   9. Click Add to open the Add Package Object Privileges page.

      
      Description of the illustration tdpii_add_package_privs.gif
      
      

   10. Enter SYS.DBMS_STREAMS_MESSAGING in the Select Package Objects field.

   11. Move EXECUTE from the Available Privileges list to the Selected Privileges list.

   12. Click OK to add the privilege.

   13. Click Apply to grant the privilege.

   14. Log out of Enterprise Manager.

   Note:

   You can also use the GRANT SQL statement to grant privileges to a user.

2. Create a trigger in the ii1.example.com database to enqueue a message automatically when a change is made to the oe.orders table.

   1. Log in to ii1.example.com database in Enterprise Manager as the Oracle Streams administrator.

   2. Go to the Database Home page.

   3. Click Schema to open the Schema subpage.

   4. Click Triggers in the Programs section.

   5. On the Triggers page, click Create.

      The Create Trigger page appears, showing the General subpage.

      
      Description of the illustration tdpii_create_trigger.gif
      
      

   6. Enter enqueue_orders in the Name field.

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

   8. Enter the following in the Trigger Body field:

      DECLARE  
        message  strmadmin.order_id_date;  
      BEGIN  
        message := strmadmin.order_id_date(  
                      order_id   => :NEW.order_id,  
                      order_date => TO_CHAR(:NEW.order_date));  
        DBMS_STREAMS_MESSAGING.ENQUEUE (  
          queue_name => 'strmadmin.streams_queue',  
          payload    => ANYDATA.CONVERTOBJECT(message));  
      END;
      

   9. Click Event to open the Event subpage.

   10. Ensure that Table is selected in the Trigger On list.

   11. Enter oe.orders in the Table (Schema.Table) field.

   12. Select After for Fire Trigger.

   13. Select Insert for Event. In this example, only Insert should be selected because the messages track new orders, not changes to existing orders.

   14. Click Advanced.

   15. Select Trigger for each row.

   16. Enter OLD in the Old as field in the Referencing section.

   17. Enter NEW in the New as field in the Referencing section.

   18. Click OK to create the trigger.

   Note:

   You can also use the CREATE TRIGGER SQL statement to create a trigger.

3. Complete the steps in "Task 4: Configuring a Messaging Client to Dequeue Messages" to continue this extended example.

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 Oracle Database 2 Day DBA 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.

Task 5: Enqueuing Messages

The example in "Task 3: Configuring a Message Enqueuing Mechanism" created a trigger. The trigger enqueues a message with information about an order that was inserted into the oe.orders table into the strmadmin.streams_queue queue. So, you can enqueue messages into the strmadmin.streams_queue queue by inserting rows into the oe.orders table.

To insert rows into the oe.orders table: 

1. On a command line, open SQL*Plus and connect to the ii1.example.com database as oe user.

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. In SQL*Plus, insert rows into the oe.orders table:

   INSERT INTO oe.orders VALUES(3000, SYSDATE, 'direct', 116, 0, 4000.00, 153, NULL);
    
   INSERT INTO oe.orders VALUES(3001, SYSDATE, 'direct', 117, 5, 5000.00, 163, NULL);
    
   INSERT INTO oe.orders VALUES(3002, SYSDATE, 'direct', 118, 7, 6000.00, 159, NULL);
   

3. In SQL*Plus, commit your changes:

   COMMIT;
   

4. Complete the steps in "Task 6: Dequeuing Messages" to continue this extended example.

Task 6: Dequeuing Messages

The example in "Task 5: Enqueuing Messages" enqueues messages into the strmadmin.streams_queue queue in the ii1.example.com database. After messages are enqueued, the propagation configured in "Task 2: Configuring the Queues and Propagation Between Them" sends the messages to the strmadmin.streams_queue queue in the ii2.example.com database.

This example dequeues the messages from the queue in the ii2.example.com database using the dequeue_orders procedure created in "Task 4: Configuring a Messaging Client to Dequeue Messages".

To dequeue messages: 

1. Optionally, verify that the messages have arrived in the strmadmin.streams_queue queue at ii2.example.com before you attempt to dequeue them. It might take time for the propagation to send messages from the queue at ii1.example.com to the queue at ii2.example.com. See "Viewing the Messages in a Queue" for instructions.

   Three messages should appear on the Messages page. Each message contains information about an order you inserted into the oe.orders table in "Task 5: Enqueuing Messages".

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

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

3. In SQL*Plus, run the dequeue_orders procedure:

   SET SERVEROUTPUT ON
   exec dequeue_orders;
   

   The output will be similar to the following:

   Order ID: 3000
   Order Date: 01-FEB-07 01.47.48.000000000 PM
   Order ID: 3001
   Order Date: 01-FEB-07 01.47.57.000000000 PM
   Order ID: 3002
   Order Date: 01-FEB-07 01.48.04.000000000 PM
   No more messages.
    
   PL/SQL procedure successfully completed.
   

Task 1: Creating the Message Type

Create a user-defined type to define the information that you want to track for the applications. This example creates the strmadmin.app_info type for messages that will contain a parameter and a value.

To create the strmadmin.app_info type: 

1. Prepare your environment for messaging if you have not already done so. See "Preparing for Messaging".

2. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

3. Go to the Database Home page.

4. Click Schema to open the Schema subpage.

5. Click Object Types in the User Defined Types section.

6. On the Object Types page, click Create to open the Create Object Type page.

   
   Description of the illustration tdpii_create_object_type.gif
   
   

7. Enter app_info in the Name field.

8. Ensure that strmadmin is selected in the Schema field.

9. Ensure that Predefined Type is selected for Datatype in the Attributes section.

10. Click Add in the Attributes section to open the Add Predefined Type Attributes page.

    
    Description of the illustration tdpii_add_type_attribute.gif
    
    

11. To add the first attribute to the app_info type:

    1. Enter parameter in the Name field.

    2. Select VARCHAR2 for Type.

    3. Enter 20 in the Length field.

    4. Click OK.

12. On the Create Object Type page, ensure that Predefined Type is selected for Datatype in the Attributes section.

13. Click Add in the Attributes section to open the Add Predefined Type Attributes page.

14. To add the next attribute to the app_info type:

    1. Enter value in the Name field.

    2. Select NUMBER for Type.

    3. Enter 12 in the Length field.

    4. Click OK.

15. On the Create Object Type page, click OK to create the type.

16. Complete the steps in "Task 2: Configuring a Queue and a Messaging Client" to continue this extended example.

Task 2: Configuring a Queue and a Messaging Client

Create a queue table and a queue to store the messages that the first application will generate for the second application. After the queue is created, message notification requires a consumer who can dequeue messages from the queue. In this example, a messaging client is the consumer who can dequeue messages from the streams_queue queue.

To configure a queue and a messaging client: 

1. Create a queue named streams_queue in the schema of the Oracle Streams administrator. See "Creating an ANYDATA Queue" for instructions.

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

   See Oracle Database 2 Day DBA 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.app_info',
       rule_condition => ':MSG.VALUE >= 0',
       streams_type   => 'DEQUEUE',
       streams_name   => 'strmadmin',
       queue_name     => 'strmadmin.streams_queue');
   END;
   /
   

   The user name for the Oracle Streams administrator must be specified for the streams_name parameter. In this example, the user name for the Oracle Streams administrator is strmadmin. The name of the new messaging client is also 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 a value greater than zero should be dequeued. So, with this rule, the messaging client will dequeue all new messages of type strmadmin.app_info that appear in strmadmin.streams_queue because all parameter values are greater than or equal to zero.

4. Complete the steps in "Task 3: Configuring a Mechanism for Dequeuing Messages" to continue this extended example.

Task 3: Configuring a Mechanism for Dequeuing Messages

In this extended example, the second application must be able to dequeue messages from the streams_queue queue. For simplicity, this example creates a PL/SQL procedure called dequeue_app_messages to dequeue messages of type strmadmin.app_info. This procedure uses the messaging client created in "Task 2: Configuring a Queue and a Messaging Client" to dequeue messages.

To configure a mechanism for dequeuing messages: 

1. Grant the EXECUTE privilege on the DBMS_AQ package to the Oracle Streams administrator.

   This example configures a procedure that runs the DEQUEUE procedure in the DBMS_AQ package. The user who runs this procedure must have explicit EXECUTE privilege on the package that contains the procedure. The privilege cannot be granted through a role. Therefore, the Oracle Streams administrator must be granted explicit EXECUTE privilege on the package.

   1. Log in to Enterprise Manager as an administrative user who can grant privileges to the strmadmin user.

   2. Go to the Server subpage.

   3. Click Users in the Security section.

      The Users page appears.

   4. Select the STRMADMIN user.

   5. Click Edit.

      The General subpage of the Edit User page appears.

   6. Click Object Privileges to open the Object Privileges subpage.

      
      Description of the illustration tdpii_edit_user.gif
      
      

   7. Select Package in the Select Object Type list.

   8. Click Add to open the Add Package Object Privileges page.

      
      Description of the illustration tdpii_add_package_privs.gif
      
      

   9. Enter SYS.DBMS_AQ in the Select Package Objects field.

   10. Move EXECUTE from the Available Privileges list to the Selected Privileges list.

   11. Click OK to add the privilege.

   12. Click Apply to grant the privilege.

   Note:

   You can also use the GRANT SQL statement to grant privileges to a user.

2. Create a PL/SQL procedure that dequeues messages.

   1. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

   2. Go to the Database Home page.

   3. Click Schema to open the Schema subpage.

   4. Click Procedures in the Programs section.

   5. On the Procedures page, click Create.

   6. On the Create Procedure page, enter dequeue_app_messages in the Name field.

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

   8. Delete the sample text in the Source field.

   9. Enter the following in the Source field:

      (
        context  ANYDATA, 
        reginfo  SYS.AQ$_REG_INFO, 
        descr    SYS.AQ$_DESCRIPTOR)
      AS 
        dequeue_options     DBMS_AQ.DEQUEUE_OPTIONS_T; 
        message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T; 
        message_handle      RAW(16); 
        message             ANYDATA; 
        app_message         strmadmin.app_info; 
        rc                  PLS_INTEGER; 
      BEGIN 
        -- Get the message identifier and consumer name from the descriptor 
        dequeue_options.msgid := descr.msg_id; 
        dequeue_options.consumer_name := descr.consumer_name; 
        -- Dequeue the message 
        DBMS_AQ.DEQUEUE( 
          queue_name         => descr.queue_name, 
          dequeue_options    => dequeue_options, 
          message_properties => message_properties, 
          payload            => message, 
          msgid              => message_handle);
        rc := message.getobject(app_message); 
        COMMIT; 
      END;
      

      A message notification PL/SQL procedure must have the following signature:

      PROCEDURE procedure_name(
        context  IN  ANYDATA,
        reginfo  IN  SYS.AQ$_REG_INFO,
        descr    IN  SYS.AQ$_DESCRIPTOR);
      

      Here, procedure_name stands for the name of the procedure. The procedure is a PLSQLCALLBACK data structure that specifies the user-defined PL/SQL procedure to be invoked on message notification.

      The procedure in this example is a simple notification procedure that dequeues a message of type strmadmin.app_info type using the message identifier and consumer name sent by the notification.

   10. Click OK to create the procedure.

   Note:

   You can also use the CREATE PROCEDURE SQL statement to create a procedure.

3. Complete the steps in "Task 4: Configuring Message Notification" to continue this extended example.

Task 4: Configuring Message Notification

In this extended example, message notification invokes the strmadmin.dequeue_app_messages procedure when a new message is enqueued into the strmadmin.streams_queue queue. This procedure dequeues the new message or messages. After dequeuing, an application can process the messages in any customized way. In this example, the second application uses the information in the messages to set application parameter values. For simplicity, this example does not configure the second application.

To configure message notification that invokes a procedure: 

1. On a command line, open SQL*Plus and connect to the database as the Oracle Streams administrator.

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Set the message notification to invoke the strmadmin.dequeue_app_messages procedure when messages are enqueued and they can be dequeued by the strmadmin messaging client:

   BEGIN
     DBMS_STREAMS_ADM.SET_MESSAGE_NOTIFICATION (
       streams_name         => 'strmadmin',
       notification_action  => 'strmadmin.dequeue_app_messages',
       notification_type    => 'PROCEDURE',
       include_notification => TRUE,
       queue_name           => 'strmadmin.streams_queue');
   END;
   /
   

3. Complete the steps in "Task 5: Enqueuing Messages and Checking for Message Notification" to continue this extended example.

Task 5: Enqueuing Messages and Checking for Message Notification

In this extended example, one application enqueues messages that are processed by a second application. For simplicity, this example enqueues messages manually. After the messages are enqueued, you can ensure that they have been dequeued automatically through message notification.

To enqueue messages and check for message notification: 

1. On a command line, open SQL*Plus and connect to the database as the Oracle Streams administrator.

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Enqueue messages into the strmadmin.streams_queue queue:

   DECLARE
     msg    strmadmin.app_info;
   BEGIN
     msg := strmadmin.app_info(
               parameter => 'THRESHOLD',
               value     => 1000);
     DBMS_STREAMS_MESSAGING.ENQUEUE (
       queue_name   => 'strmadmin.streams_queue',
       payload      => ANYDATA.CONVERTOBJECT(msg));
     COMMIT;
   END;
   / 
    
   DECLARE
     msg    strmadmin.app_info;
   BEGIN
     msg := strmadmin.app_info(
               parameter => 'MIN_WAIT',
               value     => 1);
     DBMS_STREAMS_MESSAGING.ENQUEUE (
       queue_name   => 'strmadmin.streams_queue',
       payload      => ANYDATA.CONVERTOBJECT(msg));
     COMMIT;
   END;
   /
    
   DECLARE
     msg    strmadmin.app_info;
   BEGIN
     msg := strmadmin.app_info(
               parameter => 'BUFFER_SIZE',
               value     => 30);
     DBMS_STREAMS_MESSAGING.ENQUEUE (
       queue_name   => 'strmadmin.streams_queue',
       payload      => ANYDATA.CONVERTOBJECT(msg));
     COMMIT;
   END;
   / 
   

3. Optionally, view the messages in the streams_queue queue, and verify that they have been dequeued automatically using message notification. See "Viewing the Messages in a Queue" for instructions.

   When the message notification configured in this example is working properly, you should see one of the following on the Messages page:

   • One or more of the enqueued messages might appear with Processed in the Consumers in Different States field. A Processed value in this field means that the message has been dequeued by all consumers.

   • No messages appear in the queue. Eventually, processed messages are deleted from a queue automatically. If no messages appear, then the enqueued messages were dequeued by message notification and then deleted automatically.

Viewing the Messages in a Queue

You can view the messages in a queue to ensure that an application is enqueuing messages correctly or to see which messages are currently ready to be dequeued or propagated to another queue.

To view the messages currently stored in a queue: 

1. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage Advanced Queues to open the Manage Advanced Queues page.

5. Select the queue that contains the messages. If necessary, use the search tool to find the queue, and then select it.

6. Select Messages in the Actions list.

7. Click Go in the Search tool.

8. On the Messages page, click Go. The messages are listed.

   
   Description of the illustration tdpii_messages.gif
   
   

9. Click the number link in the Consumers In Different States field to view the consumers of a message. A consumer might be a propagation that sends the message to a different queue or an application that dequeues the message.

Viewing Persistent Queue Statistics

If you are using persistent messaging mode, then messages are stored in queue table on hard disk. This topic describes viewing statistics for messages that were enqueued in persistent mode into a persistent queue.

To view persistent queue statistics: 

1. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage Advanced Queues to open the Manage Advanced Queues page.

5. Select the queue that contains the messages. If necessary, use the search tool to find the queue, and then select it.

6. Select Queue Statistics in the Actions list.

7. Click Go.

8. On the Queue Statistics page, click Message Statistics to view the persistent queue statistics for the queue.

The message statistics show the number of messages in each state in the persistent queue. They also show the total wait time and the average wait time for messages to be dequeued.

Viewing the Consumers Who Can Dequeue Messages

Consumers are configured to dequeue messages from a specific queue. In a messaging environment, consumers are represented by subscribers to a queue, and more than one consumer might use a single subscriber.

Consumers can be:

• Applications or users that dequeue and process messages

• Propagations that send messages from one queue to another

• Apply processes that can dequeue messages for custom processes in a messaging environment or dequeue changes to database objects in a replication environment

To view the consumers who can dequeue messages from a specific queue: 

1. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage Advanced Queues to open the Manage Advanced Queues page.

5. Select the queue. If necessary, use the search tool to find the queue, and then select it.

6. Select Subscribers in the Actions list.

7. Click Go to open the Subscribers page.

The Subscribers page includes the following information about each subscriber to the queue:

• The Name field contains the name of the subscriber.

• The Address field is typically populated if the subscriber is a propagation that sends messages to another queue. The Address field shows the name of the queue that receives the messages.

• The Database Link field shows the database link to a remote database if the subscriber is at the remote database.

• The Rule field shows the rule used by the subscriber. Rules can determine which messages are dequeued by the subscriber. Some subscribers do not use rules.

• The Transformation field shows the transformation used by the subscriber. Transformations modify messages while they are being dequeued. Some subscribers do not use transformations.

Correcting an ORA-01031 Error While Enqueuing or Dequeuing Messages

If a user who does not have the required privileges attempts to enqueue or dequeue messages, then Oracle Database returns the following error:

ORA-01031: insufficient privileges

The enqueue or dequeue operation fails when Oracle Database returns this message. To correct the problem, grant ENQUEUE or DEQUEUE privileges on the queue to the user.

To grant ENQUEUE or DEQUEUE privileges to a user: 

1. On a command line, open SQL*Plus and connect to the database as an administrative user, such as the Oracle Streams administrator or SYSTEM.

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Run the GRANT_QUEUE_PRIVILEGE procedure in the DBMS_AQADM package to grant the required queue privileges to the user.

   For example, to grant the ENQUEUE privilege to the hr user on the strmadmin.streams_queue queue, run the following procedure:

   BEGIN
      DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
         privilege  => 'ENQUEUE', 
         queue_name => 'strmadmin.streams_queue',
         grantee    => 'hr');
   END;
   /
   

   To grant the DEQUEUE privilege to the hr user on the strmadmin.streams_queue queue, run the following procedure:

   BEGIN
      DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
         privilege  => 'DEQUEUE', 
         queue_name => 'strmadmin.streams_queue',
         grantee    => 'hr');
   END;
   /
   

Correcting an ORA-24033 Error While Enqueuing Messages

If there are no consumers for a message when a user or application tries to enqueue it, then Oracle Database returns the following error:

ORA-24033: no recipients for message

The message is not enqueued when Oracle Database returns this message.

To correct the problem: 

1. Configure at least one consumer for the messages.

2. Enqueue the messages.

Consumers can be:

• Applications or users that dequeue and process messages

• Propagations that send messages from one queue to another

• Apply processes that can dequeue messages for custom processes in a messaging environment or dequeue changes to database objects in a replication environment

• Messaging clients that dequeue messages and pass them to an application

Correcting an ORA-02019 Error for a Propagation

A propagation uses the privileges of the database user who owns its source queue when it sends messages. If the source queue owner cannot access the database link used by the propagation, then Oracle Database returns the following error:

ORA-02019: connection description for remote database not found

The messages are not propagated when Oracle Database returns this message. The easiest way to correct this problem is to delete and re-create the database link so that the source queue owner can access it.

To delete and re-create a database link: 

1. In Oracle Enterprise Manager, log in to the database as an administrative user, such as the Oracle Streams administrator or SYSTEM.

2. Go to the Database Home page.

3. Click Schema to open the Schema subpage.

4. Click Database Links in the Database Objects section.

   The Database Links page appears.

5. Use the search tool to list the database link you want to delete and re-create.

6. Select the database link.

7. Click Delete.

8. Click Yes on the confirmation page to delete the database link.

9. Follow the instructions in "Tutorial: Creating a Database Link" to re-create the database link.

To ensure that the source queue owner for the propagation has access to the database link, specify this user as the fixed user in the Connect As section when you create the database link.

Understanding Why Dequeued Messages Remain in a Queue

A message can remain in a queue after it has been dequeued for the following reasons:

• One or more consumers of the message have not yet dequeued it. A message is not removed from a queue until all of its consumers have dequeued it.

• The message has not been cleaned up by a background process. After all of the consumers of a message have dequeued it, the message remains in the queue until a background process removes it automatically.

If you see messages in a queue that were dequeued by a consumer, then you can use Enterprise Manager to check on the consumers for a message and to determine whether all of the consumers for a message have dequeued it.

To check on messages in a queue: 

1. Follow the instructions in "Viewing the Consumers Who Can Dequeue Messages" to view the consumers who can dequeue the messages. If there are multiple consumers for a message, then some consumers might not have dequeued the message yet.

2. Follow the instructions in "Viewing the Messages in a Queue". If the Consumers in Different States field shows PROCESSED for a message, then all of the consumers of the message have dequeued it. In this case, the message remains in the queue until the background process removes it automatically.