Topics: |
You can detect an Oracle E-Business Suite event by using an RDBMS Table Listener for Oracle E-Business Suite. The Table Listener polling technology enables you to specify SQL SELECT statements to execute periodically. After data is polled, it passes through the event port for additional processing.
You can poll a relational or non-relational database directly and send the results to a file or JMS message queue. You use the following techniques to listen to an Oracle E-Business Suite event:
The listener polls a table, sends each newly inserted row to a destination you specify (known as the disposition), and uses a control table to keep track of the row that was most recently read. The control table prevents the most recently read row from being reread during the next listening cycle.
You can apply this flexible yet simple technique in most situations. For more information, see Standard Event Processing With Row Tracking.
The listener polls a table, sends each newly inserted row to a destination you specify, and then deletes the new row from the table to prevent it from being reread during the next listening cycle.
You can apply this technique when the source table is being used to pass data to the adapter, and the table rows do not need to persist. Rows are deleted as they are processed. For more information, see Standard Event Processing With Row Removal.
At design time you assign triggers to a joined group of tables. At run time the triggers write information about table changes to a common control table. The listener polls the control table and sends information about the table changes to a destination you specify. The listener deletes new rows from the control table to prevent them from being reread during the next listening cycle.
You can apply this technique when listening to events in a group of large joined tables, or when you need to know if a row has been updated or deleted. For more information, see Trigger-based Event Processing.
Configuration requirement: Copy the JDBC driver libraries if you have not yet done so to the iway7\lib directory.
How to: |
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 event key of the control table.
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 process repeats.
To implement this event processing technique, see How to Implement Standard Event Processing With Row Tracking.
To implement standard event processing with row tracking:
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.
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.
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.
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.
How to: |
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:
The process repeats itself.
To implement this event processing technique, see How to Implement Standard Event Processing With Row Removal.
To implement the standard event processing with row removal technique:
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.
How to: |
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:
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.
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.
The process repeats itself.
For a summary of how to implement this technique, see How to Implement Trigger-based Event Processing.
To implement the trigger-based event processing technique:
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.
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.
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.
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;