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:

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.
7. Select Package in the Select Object Type list.
8. Click Add to open the Add Package Object Privileges page.
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.
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.
Complete the steps in "Task 4: Configuring Message Notification" to continue this extended example.

Task 3: Configuring a Mechanism for Dequeuing Messages

Related Topics