Overview of Messaging Example

This example illustrates using a single SYS.AnyDataqueue at a database called oedb.net to create a Oracle Streams messaging environment in which events containing message payloads of different types are stored in the same queue. Specifically, this example illustrates the following messaging features of Oracle Streams:

Enqueuing messages containing order payload and customer payload as SYS.Anydata events into the queue

This example creates a new user to function as the Oracle Streams administrator (strmadmin) and prompts you for the tablespace you want to use for this user's data. Before you start this example, either create a new tablespace or identify an existing tablespace for the Oracle Streams administrator to use. The Oracle Streams administrator should not use the SYSTEM tablespace.

Set Up Users and Create a SYS.AnyData Queue

Complete the following steps to set up users and create a SYS.AnyData queue for a Oracle Streams messaging environment.

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to the database.

Run SETECHOON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/
SET ECHO ON
SPOOL streams_setup_message.out
/*

Step 2 Set Up Users

Connect to oedb.net as SYS user.

*/
CONNECT SYS/CHANGE_ON_INSTALL@oedb.net AS SYSDBA
/*

This example uses the oe sample schema. For this example to work properly, the oe user must have privileges to run the subprograms in the DBMS_AQ package. The oe user is specified as the queue user when the SYS.AnyData queue is created in Step 3. The SET_UP_QUEUE procedure grants the oe user enqueue and dequeue privileges on the queue, but the oe user also needs EXECUTE privilege on the DBMS_AQ package to enqueue events into and dequeue events from the queue.

Also, most of the configuration and administration actions illustrated in this example are performed by the Oracle Streams administrator. In this step, create the Oracle Streams administrator named strmadmin and grant this user the necessary privileges. These privileges enable the user to run subprograms in packages related to Oracle Streams, create rule sets, create rules, and monitor the Oracle Streams environment by querying data dictionary views. You can choose a different name for this user.

Note:

To ensure security, use a password other than strmadminpw for the Oracle Streams administrator.

The SELECT_CATALOG_ROLE is not required for the Oracle Streams administrator. It is granted in this example so that the Oracle Streams administrator can monitor the environment easily.

If you plan to use the Oracle Streams tool in Oracle Enterprise Manager, then grant the Oracle Streams administrator SELECTANYDICTIONARY privilege, in addition to the privileges shown in this step.

For a user to perform queue operations, such as enqueue and dequeue, on a secure queue, the user must be configured as a secure queue user of the queue. The oe_queue queue is a secure queue because it was created using SET_UP_QUEUE. This step enables the oe user to perform enqueue operations on this queue.

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to the database.

Run SETECHOON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/
SET ECHO ON
SPOOL streams_enqprocs_message.out
/*

Step 2 Create a Type to Represent Orders

Connect as oe.

*/
CONNECT oe/oe@oedb.net
/*

Create a type to represent orders based on the columns in the oe.orders table. The type attributes include the columns in the oe.orders table, along with one extra attribute named action. The value of the action attribute for instances of this type is used to determine correct action to perform on the instance (either apply process dequeue or explicit dequeue). This type is used for events that are enqueued into the SYS.AnyData queue.

Create a type to represent customers based on the columns in the oe.customers table. The type attributes include the columns in the oe.customers table, along with one extra attribute named action. The value of the action attribute for instances of this type is used to determine correct action to perform on the instance (either apply process dequeue or explicit dequeue). This type is used for events that are enqueued into the SYS.AnyData queue.

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to the database.

Run SETECHOON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/
SET ECHO ON
SPOOL streams_apply_message.out
/*

Step 2 Create a Function to Determine the Value of the action Attribute

Connect as oe.

*/
CONNECT oe/oe@oedb.net
/*

Create a function called get_oe_action to determine the value of the action attribute in the events in the queue. This function is used in rules later in this example to determine the value of the action attribute for an event. Then, the clients of the rules engine perform the appropriate action for the event (either dequeue by apply process or explicit dequeue). In this example, the clients of the rules engine are the apply process and the oe.explicit_dq PL/SQL procedure.

Create a message handler called mes_handler that will be used as a message handler by the apply process. This procedure takes the payload in a user-enqueued event of type oe.order_event_typ or oe.customer_event_typ and inserts it as a row in the oe.orders table and oe.customers table, respectively.

Step 7 Create a Rule that Evaluates to TRUE if the Event Action Is apply

Create a rule that evaluates to TRUE if the action value of an event is apply. Notice that tab.user_data is passed to the oe.get_oe_action function. The tab.user_data column holds the event payload in a queue table. The table alias for the queue table was specified as tab in Step 5.

Create a rule that evaluates to TRUE if the event in the queue is a row LCR that changes either the oe.orders table or the oe.customers table. This rule enables the apply process to apply user-enqueued changes to the tables directly. For convenience, this rule uses the Oracle-supplied evaluation context SYS.STREAMS$_EVALUATION_CONTEXT because the rule is used to evaluate LCRs. When this rule is added to the rule set, this evaluation context is used for the rule during evaluation instead of the rule set's evaluation context.

Grant EXECUTE privilege on the strmadmin.apply_oe_rs rule set. Because oe was specified as the apply user when the apply process was created in Step 10, oe needs EXECUTE privilege on the rule set used by the apply process.

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to the database.

For a user to perform queue operations, such as enqueue and dequeue, on a secure queue, the user must be configured as a secure queue user of the queue. The oe_queue queue is a secure queue because it was created using SET_UP_QUEUE. The oe user is able to perform dequeue operations on this queue when the agent is used to create a subscriber to the queue in the next step.

Add a subscriber to the oe_queue queue. This subscriber will perform explicit dequeues of events. A subscriber rule is used to dequeue any events where the action value is not apply. If the action value is apply for an event, then the event is ignored by the subscriber. Such events are dequeued and processed by the apply process.

Create a PL/SQL procedure called explicit_dq to dequeue events explicitly using the subscriber created in Step 4.

Note:

This procedure commits after the dequeue of the events. The commit informs the queue that the dequeued messages have been consumed successfully by this subscriber.

This procedure can process multiple transactions and uses two exception handlers. The first exception handler next_trans moves to the next transaction while the second exception handler no_messages exits the loop when there are no more messages.

It is possible to dequeue user-enqueued LCRs explicitly, but this example does not illustrate this capability.

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to the database.

Run SETECHOON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/
SET ECHO ON
SPOOL streams_enq_deq.out
/*

Step 2 Enqueue Non-LCR Events to be Dequeued by the Apply Process

Connect as oe.

*/
CONNECT oe/oe@oedb.net
/*

Enqueue events with apply for the action value. Based on the apply process rules, the apply process dequeues and processes these events with the oe.mes_handler message handler procedure created in "Create a Message Handler". The COMMIT after the enqueues makes these two enqueues part of the same transaction. An enqueued message is not visible until the session that enqueued it commits the enqueue.

Enqueue events with dequeue for the action value. The oe.explicit_dq procedure created in "Create a Procedure to Dequeue Events Explicitly" dequeues these events because the action is not apply. Based on the apply process rules, the apply process ignores these events. The COMMIT after the enqueues makes these two enqueues part of the same transaction.

Enqueue row LCR events. The apply process applies these events directly. Enqueued LCRs should commit at transaction boundaries. In this step, a COMMIT statement is run after each enqueue, making each enqueue a separate transaction. However, you can perform multiple LCR enqueues before a commit if there is more than one LCR in a transaction.

Enqueue and Dequeue Events Using JMS

This example enqueues non-LCR events and row LCR events into the queue using Java Message Service (JMS). Then, this example dequeues these events from the queue using JMS.

Note:

Enqueue of JMS types and XML types does not work with Oracle Streams Sys.Anydata queues unless you call DBMS_AQADM.ENABLE_JMS_TYPES(queue_table_name) after DBMS_STREAMS_ADM.SET_UP_QUEUE(). Enabling an Oracle Streams queue for these types may affect import/export of the queue table.

For this example to complete successfully, the LCR schema must be loaded into the SYS schema using the catxlcr.sql script in Oracle home in the rdbms/admin/ directory. Run this script now if it has not been run already.