Choosing a Listening Technique

Standard Event Processing With Row Tracking

The standard event processing with row tracking technique enables you to listen to the source table without removing its rows. It requires you to create a single-cell control table that keeps track of the last new row the RDBMS Table Listener for Oracle E-Business Suite read from the source table.

The control table's one column corresponds to a column (or to a group of columns) in the source table that is unique, sortable, and indicates how recently the row was added to the source table relative to the other rows. For example, the first row added to the source table has the lowest value, and the last row added has the highest value. This value is the "event key."

When you create the control table, initialize it to the event key of the row most recently added to the source table. When you specify the listener's properties, configure the listener's Post Query property to automatically update the control table's event key.

Each time the listener queries the source table, it looks for rows added since the last query—that is, for rows whose event key is greater than the current value of the field in the control table. It reads each row of this type and returns it to the specified destination using an XML document. To ensure that the row is not read again the next time the listener queries the table, the listener updates the field in the control table to match the value of the row just read from the source table.

The following figure illustrates standard event processing with row tracking. It shows the source and control tables, the listener, and the XML document at various stages.

In the previous figure:

The Table Listener queries the source table and copies each source table row whose event key is greater than the control table's event key. The listener copies the row to an XML document and sends it to the destination defined in the port disposition.
The listener updates the event key in the control table to match the row it has most recently read.
The listener copies the next source table row to an XML document.

The process repeats.

To implement this event processing technique, see How to Implement Standard Event Processing With Row Tracking.

Top of page

Procedure: How to Implement Standard Event Processing With Row Tracking

To implement standard event processing with row tracking:

Create a control table.
Configure an RDBMS Table Listener for Oracle E-Business Suite using iWay Explorer or iWay web console.
In addition to the required listener properties, for standard event processing with row tracking you must also provide values for the following optional properties:

SQL Query, the SQL SELECT statement that identifies the source table to which the adapter listens, and with which it queries the table.

Post Query, the SQL statements that maintain the field in the control table.

For instructions for configuring a listener, see Creating a Channel.

For an example of creating the control table, see the following topic.

Top of page

Example: Creating the Control Table for an Oracle E-Business Suite Event

Follow the steps in this example to create a table named TEMP_NEW_YORK_ORDER_ENTITY that has a single field named WIP_ENTITY_ID. You specify this table when you configure the RDBMS Table Listener for Oracle E-Business Suite, as described in Creating a Channel.

When discrete jobs are created through the Oracle E-Business Suite graphical interface, an entry is created in the WIP.WIP_DISCRETE_JOBS table. For this example, you configure an event to detect new entries to this table. You use the standard event processing with row tracking technique. (Oracle E-Business Suite processing cannot delete rows from the table.) To accomplish this, first create a simple table to track of the records processed.

From within Oracle SQL*PLUS, run the following SQL:
```
CREATE TABLE WIP.TEMP_NEW_WORK_ORDER_ENTITY_ID
(
   WIP_ENTITY_ID   NUMBER
)
```
This creates a single table with a single field.

Note: Oracle SQL*Plus is part of the Oracle client software. If it is not installed, contact your Oracle Database Administrator.

You must be logged in under the APPS schema or a similar ID that has access rights to the Oracle E-Business Suite WIP schema.
Create a single record in this table and seed it with the highest WIP_ENTITY_ID ID from your system. You can obtain this from the WIP.WIP_DISCRETE_JOBS table.
This sets the value at which to start detecting events as records enter the WIP_DISCRETE_JOBS table.

After you create a simple table in Oracle, you must configure the Table Listener, as described in Creating a Channel.

Standard Event Processing With Row Removal

The standard event processing with row removal technique assumes that the source table is being used as a conduit to pass the data to the adapter, and that the table rows do not need to persist. The RDBMS Table Listener for Oracle E-Business Suite periodically queries the source table. When it finds a row, it reads it and returns it to the Reply_to destination via an XML document. To ensure that the row is not read again when the Table Listener next queries the table, the listener then deletes the row from the table.

The following figure illustrates standard event processing with row removal. It shows the application, source table, listener, and the XML document at various stages in the event processing.

In the previous figure:

Your application inserts a new row into the source table.
The listener queries the source table and copies the new row to an XML document and sends it to the destination defined in the port disposition using the File protocol.
The listener deletes the source table row to ensure that the row is not read again when the listener next queries the table.
The application inserts a new row into the source table.

The process repeats itself.

To implement this event processing technique, see How to Implement Standard Event Processing With Row Removal.

Top of page

Procedure: How to Implement Standard Event Processing With Row Removal

To implement the standard event processing with row removal technique:

Configure an RDBMS Table Listener.
In addition to the required listener properties, provide values for the following optional properties:
SQL Query, the SQL SELECT statement that identifies the source table to which the adapter listens, and with which it queries the table.

Issue Post Query Delete, which automatically deletes each record after it was read.

For detailed instructions for configuring a listener, see Creating a Channel. For information on SQL post query parameters, see The Post Query Parameter Operators.

Trigger-based Event Processing

Trigger-based event processing is a technique for listening to multiple joined Oracle E-Business Suite tables. It is also helpful for detecting when a row was deleted or updated.

The trigger-based technique provides the following benefits:

Improved performance when listening to events in a group of large joined tables
When processing joined tables, Oracle creates a Cartesian product working table. When the joined tables are large, the interim working table is very large. The standard technique of processing Oracle E-Business Suite events, in which the adapter periodically listens to the entire structure of joined tables, can consume a significant amount of computing resources.

The trigger-based technique avoids this overhead by requiring the RDBMS Table Listener for Oracle E-Business Suite to query a single small control table and by writing to the control table only when an event actually occurs.
Increased number of event types that the adapter recognizes
Using the trigger-based technique, you can tell when a row was updated, deleted, or inserted. Using the standard technique, you can tell only when a row was inserted.

To use the trigger-based technique, you assign a trigger to each table that you want to monitor. When a value changes, it fires the corresponding trigger, which writes data to a control table. The iWay Application Adapter for Oracle E-Business Suite listens to this control table by running a query against it. When it finds a row in the control table, it reads it and returns it to the port disposition created when the port is configured via an XML document. To ensure that the row is not read again when the listener next queries the table, the listener then deletes the row from the table.

The trigger-based technique enables you to recognize changes to an entity. For the purposes of this discussion, an entity is a real-world object that is represented in the database by a hierarchical set of tables.

You manage the triggers using SQL*Plus or a similar tool and configure the event using iWay Explorer.

The following five figures illustrate the steps involved in trigger-based event processing. They show the source and control tables, the listener, the application, and the XML document at various stages in the event processing.

Your application updates a row in a group of related source tables.
The update causes a row trigger to fire in the changed table. The trigger inserts a row into the control table. The new control table row includes the key value (25), the type of transaction (update), and the new cell value (orange).
The listener queries the control table and copies the new row to an XML document. It sends the document to the destination defined in the port disposition.
The listener deletes the control table row to ensure that the row is not read again when the listener next queries the table.
The application inserts a new row into one of the source tables.

The process repeats itself.

For a summary of how to implement this technique, see How to Implement Trigger-based Event Processing.

Top of page

Procedure: How to Implement Trigger-based Event Processing

To implement the trigger-based event processing technique:

Create the control table.
The purpose of the control table is to capture the key of each entity that changed, regardless of which of the entity tables changed.

You can store a variety of information in the control table, including the key of the entity that was inserted, updated, or deleted, and the name of the table and field that was updated.

The design of the control table is a function of the business logic of your application. For example, you can choose between creating one control table for a group of joined source tables or one control table per source table. Among the issues to consider are the kinds of events to monitor (insertions, deletions, and/or updates), and whether you want to monitor only the highest-level table in a group of joined tables or all of the tables in the group.
Assign triggers to the source tables.
The triggers you assign, and to which tables you assign them, is determined by what kind of change you want to monitor. The triggers implement much of the event-processing logic.

For example, consider a bill of material scenario. (A bill of materials is a list of all the parts required to manufacture an item, the subparts required for the parts, and so on. The complete item/parts/subparts relationship can extend to several levels, creating a data structure like a tree with the finished item as the root.) In a bill of materials, where each level in the parts hierarchy is represented by a separate table, you might assign a trigger to only the highest-level table (the finished product), or you might assign triggers to all tables (the finished product and its parts and subparts).

As another example, if multiple changes are made to the same row during one listener cycle, you could configure the event adapter to record all the changes. If a row was inserted and then updated, both changes would be logged.
Configure the RDBMS Table Listener for Oracle E-Business Suite when creating a channel using iWay Explorer.
In addition to the required listener properties, for trigger-based event processing you also must provide values for the following optional properties:

SQL Query, the SQL SELECT statement that identifies the control table to which the adapter listens, and with which it queries the table to determine changes in the source tables.

Post Query, to identify the rows that the adapter automatically deletes from the control table.

For detailed instructions for configuring a listener, see Creating a Channel.

Top of page

Example: Trigger on WIP_ENTITY_NAME Column

The following trigger fires when a change is made to the WIP_ENTITY_NAME column of the WIP.WIP_ENTITIES table. When it fires, it writes the relevant values to the control table IWAY.IWAY_PO_CDC.

CREATE OR REPLACE TRIGGER IWAY.IWAY_PO_CDC_WE_TRG
AFTER INSERT OR DELETE OR UPDATE OF WIP_ENTITY_NAME
ON WIP.WIP_ENTITIES
FOR EACH ROW
BEGIN
IF INSERTING THEN
   INSERT INTO IWAY.IWAY_PO_CDC
      VALUES (
         :NEW.WIP_ENTITY_ID,
         :NEW.ORGANIZATION_ID,
             'UPDATE');
ELSE
   INSERT INTO IWAY.IWAY_PO_CDC
      VALUES (
         :OLD.WIP_ENTITY_ID,
         :OLD.ORGANIZATION_ID,
             'UPDATE');
END IF;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      NULL;         -- Record already exists
END;