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 Starting SQL*Plus and Connecting to the Database 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 check to see if 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.