Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Receiving Polling-based Data-changed Messages

This topic was last updated on: June 05, 2009

The Microsoft BizTalk Adapter 3.0 for Oracle Database supports receiving polling-based data-changed messages by polling the Oracle database tables and views. The adapter delivers the messages to your application by:

  • Periodically executing a SQL SELECT query against an Oracle table or view.

  • Executing an optional post-poll PL/SQL code block on the Oracle database. This code block is often used to update a field on the queried records in the target or to move the queried records to another table or view.

  • Returning the query results in a strongly-typed result set by invoking the POLLINGSTMT operation on your application.

The adapter executes all of these operations inside of an Oracle transaction.

You configure the Oracle Database adapter to poll the Oracle database by setting binding properties that determine polling parameters like the SQL SELECT statement that is used for the query, the polling interval, and the transaction isolation level.

The adapter also enables you to receive data-changes messages for multiple Oracle artifacts in the same application by exposing a PollingId parameter in the connection URI. This parameter modifies the target namespace of the POLLINGSTMT operation.

The following sections provide information about:

  • How to modify the target namespace of the POLLINGSTMT operation.

  • How to configure the adapter to receive polling-based data-change messages using binding properties.

  • The operations performed by the adapter.

You can modify the target namespace of the POLLINGSTMT operation by setting the PollingId query string parameter in the connection URI. If a PollingId is specified in the connection URI, the Oracle Database adapter appends the string specified in the PollingId parameter to the default target namespace for the POLLINGSTMT operation: http://microsoft.lobservices.oracledb/2007/03/POLLINGSTMT. The message action of the POLLINGSTMT operation is not modified.

For example, if the following connection URI is specified: OracleDb://User=SCOTT;Password=TIGER@Adapter?PollingId=AcctActivity, the target namespace will be http:/microsoft.lobservices.oracledb./2007/03/POLLINGSTMTAcctActivity.

By providing a unique namespace for each POLLINGSTMT operation, you can receive data-changed messages for multiple Oracle tables and views in your application.

For more information about the Oracle Database adapter connection URI, see The Oracle Database Connection URI.

You configure the Oracle Database adapter to receive data-changed messages by setting some or all of the following binding properties.

Binding Property Value Default Required/Optional

PollingStatement

Set to the SQL SELECT statement that should be executed against the Oracle database. This statement should include a FOR UPDATE clause.

You must also set this property when you connect to the Oracle database to retrieve metadata; for example, using the Add Adapter Service Reference Visual Studio Plug-in or the Consume Adapter Service BizTalk Project Add-in. This is because the adapter uses the SQL SELECT statement to generate metadata for the strongly-typed record set that the POLLINGSTMT operation returns. When you are connecting to retrieve metadata, it is not necessary to specify a FOR UPDATE clause in the polling statement.

null

Required. Setting PollingStatement to a non-null value enables polling.

PostPollStatement

Set to an optional PL/SQL code block that is executed by the adapter after the query is performed, but before the query data is returned to the client.

null

Optional. If no value is specified, a post poll statement is not executed.

PollingInterval

Set to the interval, in seconds, at which you want the adapter to query the Oracle database. This property specifies the polling interval and the polling transaction time out. The value should be greater than the amount of time it takes to execute the query and post-poll statement (if one is specified) on the Oracle database plus the amount of time it takes for the client to process the query data and return the POLLINGSTMT response message.

500

Required. If not explicitly set, the default value will apply.

PollingRetryCount

Specifies the number of times that polling is retried if an exception is encountered during the polling transaction.

Cc185357.note(en-US,BTS.10).gifNote
If the TransactionIsolationLevel binding property is set to Serializable, you may get an Oracle exception like "ORA-08177: can't serialize access for this transaction." In such a case, you must set the PollingRetryCount binding property to define the number of times you want the adapter to retry the same transaction.

0

Optional. If no value is specified, the default value is used.

TransactionIsolationLevel

Set to the isolation level with which you want the polling transaction to be created on the Oracle database. This level is dependent on your environment. The supported values are ReadCommitted and Serializable.

Cc185357.note(en-US,BTS.10).gifNote
In scenarios where there is parallel access and modification to the table being polled, you must set this property to Serializable.

ReadCommitted

Required. If not explicitly set, the default value will apply.

Cc185357.note(en-US,BTS.10).gifNote
If you are using the WCF service model or the WCF channel model, you must also set the AcceptCredentialsInUri binding property.

For more detailed information about these binding properties, see Working with BizTalk Adapter 3.0 for Oracle Database Binding Properties.

Specifying a FOR UPDATE Clause in the Polling Statement

You should specify a FOR UPDATE clause in the polling statement. The Oracle Database adapter executes the polling statement and the post-poll statement (if specified) inside an Oracle transaction. Specifying a FOR UPDATE clause ensures that the records selected by the polling statement records are locked during the transaction and that the post-poll statement can perform any required updates on them.

Cc185357.Caution(en-US,BTS.10).gifCaution
You can have scenarios where in the time window between the polling and post-poll statements, more records are added to the table that meet the condition of the post-poll statement. In such situations, the post-poll statement would update all the records that satisfy the condition and not just the records selected as part of the polling statement.

If a post-poll statement is specified and the polling statement does not contain a FOR UPDATE clause, you will experience one of the following two conditions:

  • If TransactionIsolationLevel is set to ReadCommitted, the post-poll query will not update the selected rows.

  • If TransactionIsolationLevel is set to Serializable, the following target system exception (Microsoft.ServiceModel.Channels.Common.TargetSystemException) will occur when the post-poll statement is executed: "ORA-08177 can't serialize access for this transaction". In such a case, you must set the PollingRetryCount binding property to define the number of times you want the adapter to retry the same transaction.

Recommendations for Setting the TransactionIsolatioLevel Binding Property for Polling Scenarios

Setting the TransactionIsolationLevel binding property to ReadCommitted does not ensure repeatable reads in Oracle database. In the polling scenario, adapter clients would usually specify a polling query followed by a post poll query, which are executed by the adapter in a transaction. For the post poll query to update or delete only the rows that were selected by the polling query, repeatable reads is a requirement, especially when there is concurrent access from many different threads on the table being polled. Hence the transaction isolation level that the adapter should use should be serializable. You can achieve this by setting the TransactionIsolationLevel binding property to Serializable for receive scenarios.

Specifying a NOWAIT Clause in the Polling Statement

You may have scenarios where concurrent threads are accessing the table being polled, leading to too many contentions in the table. This may cause the polling query to be blocked to get a lock on table rows. Adapter clients may want to specify a NOWAIT keyword along with the FOR UPDATE keyword in the polling query. This will cause the polling query execution within the adapter to return immediately if there are locks on rows which the polling query is trying to select. An exception is usually thrown by Oracle under such conditions. Again, adapter clients may use the PollingRetryCount binding property (defaulted to 0) to enable certain number of retries before exiting. The number of retries is specified by the value of the PollingRetryCount binding property.

An example of a polling query with the NOWAIT option is:

SELECT * from EMP WHERE FLAG = 'Y' FOR UPDATE NOWAIT

The following steps describe how the Oracle Database adapter performs polling for receiving data-changed messages:

  1. The Oracle Database adapter creates a transaction with the isolation level specified by TransactionIsolationLevel. The transaction timeout is set to the interval specified by PollingInterval.

  2. The Oracle Database adapter executes the query specified by PollingStatement. The polling interval specifies the time interval in which to execute the polland post-poll queries. The polling query must complete with enough time remaining for the post-poll statement to complete (if specified) and for the adapter client to process the result set returned by the query and return a POLLINGSTMT response message to the adapter.

  3. If PostPollStatement is not null, the Oracle Database adapter executes the specified PL/SQL code block on the Oracle database. The adapter blocks until the code block finishes executing. The polling and post poll statement must be run within the time specified for the polling interval. However, the post-poll statement must complete with enough time remaining for the adapter client to process the result set returned by the query and return a POLLINGSTMT response message to the adapter.

  4. The Oracle Database adapter returns the query results from step two to the adapter client in a POLLINGSTMT message that is sent over an inbound (IReplyChannel) channel.

  5. The adapter client processes the polling data and replies with a POLLINGSTMT response message over the same channel to indicate that it has finished processing, or with a fault message to indicate an error.

  6. If the Oracle Database adapter receives:

    1. A POLLINGSTMT response message, it commits the transaction.

    2. A fault message, it terminates the transaction.

If the Oracle Database adapter does not receive the POLLLINGSTMT response message within the time specified by PollingInterval, the transaction times out and is terminated.

In a production environment, polling can be used to monitor the data changes in the Oracle database. These data-changed messages are received by the adapter client using the Oracle Database adapter. Based on business scenarios, it can be critical that the data-changed messages are received by the adapter client in the right order.

The Oracle Database adapter supports ordered delivery or first-in-first-out (FIFO) to maintain the order in which messages are received from the Oracle database. Here are a few considerations related to support for FIFO in inbound scenarios for the Oracle Database adapter.

  • If the message is being consumed by an orchestration, the orchestration must have the ordered delivery set for the messages coming from the Oracle Database adapter receive port.

  • If the message is being consumed by a send port (in a content-based routing) scenario, the send port must have ordered delivery set for the messages coming from the Oracle Database adapter receive port.

The WCF-Custom adapter has a property Suspend message on failure that specifies whether to suspend the request message that fails inbound processing. This property can be set on the Messages tab of the WCF-Custom receive port under the Error Handling section. The following table lists the scenarios describing how the incoming messages are processed based on whether this property is set and the state of the message subscriber (orchestration or port).

WCF-Custom port property Subscriber in Unenlisted state Subscriber in Enlisted but Stopped state

Suspend message on failure property NOT set

  • Routing Failure Report is generated as a suspended (non-resumable message)

  • Actual message is not suspended

  • Post poll query is not executed as transaction gets aborted. Hence polling repeats and fetches the rows again.

  • Errors reported in the event log to describe what has happened.

  • Not considered a “Failure”. There are no error messages in the event log.

  • Actual message is put into the suspended (resumable) queue.

  • When the subscribing port or orchestration starts, the messages are automatically resumed. If ordered delivery is set on the subscriber, it will be honored.

  • The messages may also be resumed manually.

Suspend message on failure property IS set

  • Routing Failure Report is generated as a suspended (non-resumable message)

  • Actual message is also suspended

  • Post poll query is not executed as transaction gets aborted. Hence polling repeats and fetches the rows again.

  • Errors reported in the event log to describe what has happened.

  • Not considered a “Failure”. There are no error messages in the event log.

  • Actual message is put into the suspended (resumable) queue.

  • When the subscribing port or orchestration starts, the messages are automatically resumed. If ordered delivery is set on the subscriber, it will be honored.

  • The messages may also be resumed manually.

The results of the polling query are returned to the Oracle Database adapter by the inbound POLLINGSTMT operation. The result set returned in the POLLINGSTMT operation is strongly-typed.

For more information about how to receive polling-based data-changed messages using:

Show:
© 2015 Microsoft