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:

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

Task 3: Configuring a Message Enqueuing Mechanism

Related Topics