iWay Application Adapter for Oracle E-Business Suite User's Guide > Listening for Oracle E-Business Suite Events > Creating a Channel

Creating a Channel

Topics:

How to:

The following procedure describes how to use iWay Explorer (Java Swing) to create a channel for an Oracle E-Business Suite event. All defined event ports must be associated with a channel.

Procedure: How to Create a Channel

To create a channel using iWay Explorer:

  1. In the left pane, expand the iWay Events node below the configuration you created, for example, SampleConfig.

    The list of adapters appears.

  2. Expand the adapter node, for example, Oracle.

    The following image shows the Ports and Channels nodes that appear under the Oracle node.

  3. Right-click Channels.

    The following image shows the option (Add Channel) that is available when you right-click Channels.

  4. Select Add Channel.

    The following image shows the Add Channel dialog box that opens, where you supply information about the channel.

    1. In the Name field, type a name for the channel, for example, OracleChannel.
    2. In the Description field, type a brief description.
    3. From the Protocol drop-down list, select a server that supports event handling.
    4. From the Available Ports area, select a port you want to assign to this channel and click the right arrow to move it to the Selected Ports area.
  5. Click Next.

    The following image shows a sample Oracle Table Listener dialog box that opens, where you supply information about the table listener and provide parameters.

  6. Enter the values according to the information in the following table.

    The following table lists and describes the required parameters for the JDBC Thin Driver.

    Field

    Description

    Host

    Name of the server on which the Oracle E-Business Suite database instance resides.

    Port

    Port number on which the database is listening.

    SID

    Unique name of the database service, chosen by the database administrator or the person who installed Oracle E-Business Suite.

    User

    Oracle database user ID to access the Oracle database underlying the Oracle E-Business Suite system. The user ID must have database access to the interface tables being accessed.

    Password

    Password associated with the specified user ID.

    Polling Interval

    Interval, in milliseconds, at which to check for new input.

    SQL Query

    SQL SELECT statement that the listener issues to poll the table.

    If the SQL statement includes a date column or long text column, you must provide a value for the SQL Post-query parameter. The value you provide must not contain a date column or a long text column. This applies whether you provide an SQL statement here or rely upon the default.

    For example, the following SELECT statement retrieves all unprocessed records from the DISCRETE_JOBS table: 

    SELECT * FROM WIP_DISCRETE_JOBS D
WHERE DJ.WIP_ENTITY_ID >
(SELECT WIP_ENTITY_ID
FROM WIP.TEMP_NEW_WORK_ORDER_ENTITY_ID)

    Important: When a SQL Query joins two or more tables, a SQL Post Query must be used. Also, do not use a semicolon at the end of a SQL statement for a SQL Query or a SQL Post Query.

    Post Query

    A SQL statement that is executed after each new record is read from the table. Case sensitive: the case used to specify the column names must match the case used in the SELECT statement that polled the table. If the SQL Query property was omitted so that a default SELECT statement polled the table, the case used to specify the column names must match the case used to define the columns in the DBMS native schema.

    If you do not specify a value for SQL Post-query, each record read from the table is deleted after it is read. How this happens depends on whether you specify the Delete Keys property. If you:

    Specify the Delete Keys property, by default the adapter issues a DELETE statement with a WHERE clause containing every key column specified for the Delete Keys property.

    At run time this is faster than if you had not specified the Delete Keys property if there is an index on the key or if there are fewer key columns than there are columns in the SELECT statement that polled the table.

    Do not specify the Delete Keys property, by default the adapter issues a DELETE statement with a WHERE clause that specifies every column from the SELECT statement that polled the table.

    You can choose to retain the table data after it is read by specifying a value for this parameter, as shown in the examples that follow.

    Note: The SQL Post-query and Delete Keys parameters are mutually exclusive, because Delete Keys applies to the default DELETE statement, and SQL Post-query overrides the default DELETE statement. You can provide a value for one or the other, but not for both.

    There are two field operators, ? and ^, that you can use in a post-query SQL statement.

    Important: When a SQL Query joins two or more tables, a SQL Post Query must be used. Also, do not use a semicolon at the end of a SQL statement for a SQL Query or a SQL Post Query.

    Delete Keys

    Comma-separated list of key columns to be used in the default DELETE statement. DELETE operates on keys, so specify the table key columns.

    This is case sensitive: the case used to specify the column names must match the case used in the SELECT statement that polled the table. If the SQL Query property was omitted so that a default SELECT statement polled the table, the case used to specify the column names must match the case used to define the columns in the DBMS native schema.

    Note: The Delete Keys and SQL Post Query parameters are mutually exclusive, because Delete Keys applies to the default DELETE statement, and SQL Post Query overrides the default DELETE statement. You can provide a value for one or the other, but not for both. For more information, see the description of the SQL Post-query parameter in this table.

  7. After you provide the required information, click OK.

    The following image shows the channel you created beneath the Channels node in the left pane. In this example, the channel is named OracleChannel.

    In the right pane, a table summarizes the information associated with the channel.

    You are ready to start the channel to listen for events.

  8. In the left pane, right-click the channel, for example, OracleChannel, and select Start.

    The channel you created is now active.

    1. To stop the channel at any time, right-click the channel.
    2. Select Stop.

Procedure: How to Edit a Channel

To edit an existing channel:

  1. In the left pane, right-click the channel you want to edit, for example, OracleChannel.
  2. Select Edit.

    The Edit Channel dialog opens.

  3. Make the required changes to the channel configuration and click OK.

Procedure: How to Delete a Channel

To delete an existing channel:

  1. In the left pane, right-click the channel you want to delete, for example, OracleChannel.
  2. Select Delete.
    The channel disappears from the list in the left pane.

The Post Query Parameter Operators

You can use two special field operators, ? and ^, with the Post Query parameter. Both of these operators dynamically substitute database values in the SQL post-query statement at run time.

  • ?fieldname is evaluated at run time as field = value

    The ? operator is useful in UPDATE statements: 

    UPDATE table WHERE ?field

    For example, the following statement

    UPDATE Stock_Prices_Temp WHERE ?RIC

    might be evaluated at run time as:

    UPDATE Stock_Prices_Temp WHERE RIC = 'PG'
  • ^fieldname is evaluated at run time as value

    The ^ operator is useful in INSERT statements:

    INSERT INTO table VALUES (^field1, ^field2, ^field3, ...)

    For example, the following statement

    INSERT INTO Stock_Prices_Temp VALUES (^RIC, ^Price, ^Updated)

    might be evaluated at run time as:

    INSERT INTO Stock_Prices_Temp VALUES ('PG', 88.62, '2003-03-18 16:24:00.0')