Poll Oracle E-Business Suite using SELECT statement with the WCF service model

Article
02/02/2021

You can configure the Oracle E-Business adapter to receive periodic data-change messages by using a SELECT statement to continuously poll the interface tables, interface views, tables and views in Oracle E-Business Suite. You can specify a SELECT statement as a polling statement that the adapter executes periodically to poll Oracle E-Business Suite. You can also specify a post-poll PL/SQL code block that the adapter executes after the polling statement is executed.

To enable polling, you must specify certain binding properties as described in this topic. For more information about how the adapter supports polling, see Support for Inbound Calls Using Polling.

Configuring a Polling Operation with Oracle E-Business Suite Adapter Binding Properties

The following table summarizes the Oracle E-Business adapter binding properties that you use to configure the adapter to receive data change messages. You must specify these binding properties while running the polling application.

Binding Property	Description
InboundOperationType	Specifies whether you want to perform Polling or Notification inbound operation. Default is Polling.
PolledDataAvailableStatement	Specifies the SQL statement that the adapter executes to determine whether any data is available for polling. Only if a record is available, the SELECT statement you specify for the PollingInput binding property will be executed.
PollingInterval	Specifies the interval, in seconds, at which the Oracle E-Business adapter executes the statement specified for the PolledDataAvailableStatement binding property. The default is 30 seconds. The polling interval determines the time interval between successive polls. If the statement is executed within the specified interval, the adapter sleeps for the remaining time in the interval.
PollingInput	Specifies the polling statement. To poll using a SELECT statement, you must specify a SELECT statement for this binding property. The default is null. You must specify a value for PollingInput binding property to enable polling. The polling statement is executed only if there is data available for polling, which is determined by the PolledDataAvailableStatement binding property.
PollingAction	Specifies the action for the polling operation. You can determine the polling action from the service interface generated for the operation using the Add Adapter Service Reference Visual Studio Plug-in.
PostPollStatement	Specifies a statement block that is executed after the statement specified by the PollingInput binding property is executed.
PollWhileDataFound	Specifies whether the Oracle E-Business adapter ignores the polling interval and continuously executes the polling statement, if data is available in the table being polled. If no data is available in the table, the adapter reverts to execute the polling statement at the specified polling interval. Default is false.

For a more complete description of these properties, see Read about the BizTalk Adapter for Oracle E-Business Suite binding properties. For a complete description of how to use the Oracle E-Business adapter to poll the Oracle database, read further.

How This Topic Demonstrates Polling

In this topic, to demonstrate how the Oracle E-Business adapter supports receiving data change messages using SELECT statements, you poll the MS_SAMPLE_EMPLOYEE interface table in the Application Object Library application. This table is created when you run the create_apps_artifacts.sql script provided with the samples to create these objects in Oracle E-Business Suite.

To demonstrate a polling operation, we do the following:

Specify a SELECT statement for the PolledDataAvailableStatement binding property to determine where the interface table being polled (MS_SAMPLE_EMPLOYEE) has any data. In this example, you can set this binding property as:
```
SELECT COUNT (*) FROM MS_SAMPLE_EMPLOYEE
```
This ensures that the adapter executes the polling statement only when the MS_SAMPLE_EMPLOYEE interface table has some records.
Specify a SELECT statement for the PollingInput binding property. This statement retrieves all the rows in the MS_SAMPLE_EMPLOYEE interface table. In this example, you can set this binding property as:
```
SELECT * FROM MS_SAMPLE_EMPLOYEE FOR UPDATE
```
Note

For information about the FOR UPDATE clause used in the SELECT statement, see Receive polling-based data-changed messages from Oracle E-Business Suite.
Specify a DELETE statement as part of the PostPollStatement binding property. This statement will delete all data from MS_SAMPLE_EMPLOYEE interface table. In this example, you can set this binding property as:
```
DELETE FROM MS_SAMPLE_EMPLOYEE
```
After this happens, the next time the statement specified for PollingInput will be executed, it will not fetch any data.
Until more data is added to the MS_SAMPLE_EMPLOYEE interface table, you will not get any polling messages so you must repopulate the MS_SAMPLE_EMPLOYEE interface table with new records. You can do so by running the insert_apps_data.sql script provided with the samples. After you run this script, the next polling operation will fetch the new records inserted into the table.

Configuring Polling in the WCF Service Model

To poll an interface table using the Oracle E-Business adapter with the WCF service model, you must:

Generate a WCF service contract (interface) for the Poll operation on the MS_SAMPLE_EMPLOYEE interface table. To do this, you could use the Add Adapter Service Reference Plug-in.
Implement a WCF service from this interface.
Host this WCF service using a service host (System.ServiceModel.ServiceHost).

About the Examples Used in this Topic

The examples in this topic poll the MS_SAMPLE_EMPLOYEE interface table. A script to generate the table is supplied with the samples. For more information about the samples, see Samples for the Oracle EBS adapter. A sample, SelectPolling_ServiceModel, which is based on this topic, is also provided with the Oracle E-Business adapter samples.

The WCF Service Contract and Class

You can use the Add Adapter Service Reference Plug-in to create a WCF service contract (interface) and supporting classes for the Polling operation. For more information about generating a WCF service contract, see Generate a WCF client or a WCF service contract for Oracle E-Business Suite solution artifacts.

The WCF Service Contract (Interface)

The following code shows the WCF service contract (interface) generated for the Poll operation on MS_SAMPLE_EMPLOYEE interface table.

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(Namespace="http://schemas.microsoft.com/OracleEBS/", ConfigurationName="InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE")]
public interface InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE {

    // CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE_EMPLOYEE) of message Poll
    // does not match the default value (https://schemas.microsoft.com/OracleEBS/)
    [System.ServiceModel.OperationContractAttribute(IsOneWay=true, Action="InterfaceTables/Poll/FND/APPS/MS_SAMPLE_EMPLOYEE")]
    void Poll(Poll request);
}

The Message Contracts

Following is the message contract for the Poll operation on MS_SAMPLE_EMPLOYEE interface table.

[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.MessageContractAttribute(WrapperName="Poll", WrapperNamespace="http://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE" +
    "_EMPLOYEE", IsWrapped=true)]
public partial class Poll {

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE" +
        "_EMPLOYEE", Order=0)]
    public schemas.microsoft.com.OracleEBS._2008._05.TableViewRecord.APPS.MS_SAMPLE_EMPLOYEE.SelectRecord[] DATA;

    public Poll() {
    }

    public Poll(schemas.microsoft.com.OracleEBS._2008._05.TableViewRecord.APPS.MS_SAMPLE_EMPLOYEE.SelectRecord[] DATA) {
        this.DATA = DATA;
    }
}

WCF Service Class

The Add Adapter Service Reference Plug-in also generates a file that has a stub for the WCF service class implemented from the service contract (interface). The name of the file is OracleEBSBindingService.cs. You can insert the logic to process the Poll operation directly into this class. The following code shows the WCF service class generated by the Add Adapter Service Reference Plug-in.

namespace OracleEBSBindingNamespace {

    public class OracleEBSBindingService : InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE {

        // CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE_EMPLOYEE) of message Poll
        // does not match the default value (https://schemas.microsoft.com/OracleEBS/)
        public virtual void Poll(Poll request) {
            throw new System.NotImplementedException("The method or operation is not implemented.");
        }
    }
}

Receiving Inbound Messages for the Poll Operation Using a SELECT Statement

This section provides instructions on how to write a .NET application to receive inbound polling messages using the Oracle E-Business adapter.

To receive polling messages using a SELECT statement

Use the Add Adapter Service Reference Plug-in to generate a WCF service contract (interface) and helper classes for the Poll operation on the MS_SAMPLE_EMPLOYEE interface table. For more information, see Generate a WCF client or a WCF service contract for Oracle E-Business Suite solution artifacts. You can optionally specify the binding properties while generating the service contract and helper classes. This guarantees that they are properly set in the generated configuration file.

Implement a WCF service from the interface and helper classes generated in step 1. The Poll method of this class can throw an exception to abort the polling transaction, if an error is encountered processing the data received from the Poll operation; otherwise the method does not return anything. You must attribute the WCF service class as follows:

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

Within the Poll method, you can implement your application logic directly. This class can be found in OracleEBSBindingService.cs. This code in this example sub-classes the OracleEBSBindingService class. In this code, the polling message received and is written to the console.

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

public class PollingService : OracleEBSBindingNamespace.OracleEBSBindingService
{
    public override void Poll(Poll request)
    {
        Console.WriteLine("\nNew Polling Records Received");
        Console.WriteLine("*************************************************");
        Console.WriteLine("Emp No\tName\tDesignation\tSalary");
        for (int i = 0; i < request.DATA.Length; i++)
        {
            Console.WriteLine("{0}\t{1}\t{2}\t{3}",
            request.DATA[i].EMP_NO,
            request.DATA[i].NAME,
            request.DATA[i].DESIGNATION,
            request.DATA[i].SALARY);
        }
        Console.WriteLine("*************************************************");
        Console.WriteLine("\nHit <RETURN> to stop polling");
    }
}

You must implement the following class to avoid passing credentials as part of the URI. In the latter part of the application, you will instantiate this class to pass on the credentials.

class PollingCredentials : ClientCredentials, IServiceBehavior
{
    public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
    {
        bindingParameters.Add(this);
    }

    public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    protected override ClientCredentials CloneCore()
    {
        ClientCredentials clone = new PollingCredentials();
        clone.UserName.UserName = this.UserName.UserName;
        clone.UserName.Password = this.UserName.Password;
        return clone;
    }
}

Create an OracleEBSBinding and configure the polling operation by specifying the binding properties. You can do this either explicitly in code or declaratively in configuration. At a minimum, you must specify the InboundOperationType, PolledDataAvailableStatement, PollingInput, and PollingAction binding properties.

OracleEBSBinding binding = new OracleEBSBinding();
binding.InboundOperationType = InboundOperation.Polling;
binding.PolledDataAvailableStatement = "SELECT COUNT (*) FROM MS_SAMPLE_EMPLOYEE";
binding.PollingInput = "SELECT * FROM MS_SAMPLE_EMPLOYEE FOR UPDATE";
binding.PollingAction = "InterfaceTables/Poll/FND/APPS/MS_SAMPLE_EMPLOYEE";
binding.PostPollStatement = "DELETE FROM MS_SAMPLE_EMPLOYEE";

Because you are polling an interface table, you must also set the applications context. For more information about application context and the binding properties required for setting application context, see Set application context.
```
binding.OracleUserName = "myOracleEBSUserName";
binding.OraclePassword = "myOracleEBSPassword";
binding.OracleEBSResponsibilityName = "myOracleEBSResponsibility";
```

Specify Oracle E-Business Suite credentials by instantiating the PollingCredentials class you created in Step 3.

PollingCredentials credentials = new PollingCredentials();
credentials.UserName.UserName = "<Enter user name here>";
credentials.UserName.Password = "<Enter password here>";

Create an instance of the WCF service created in step 2.

// create service instance
PollingService service = new PollingService();

Create an instance of System.ServiceModel.ServiceHost by using the WCF service and a base connection URI. The base connection URI cannot contain the inbound ID, if specified. You must also pass the credentials here.
```
// Enable service host
Uri[] baseUri = new Uri[] { new Uri("oracleebs://ebs_instance_name") };
ServiceHost serviceHost = new ServiceHost(service, baseUri);
serviceHost.Description.Behaviors.Add(credentials);
```
Add a service endpoint to the service host. To do this:
- Use the binding created in step 4.
- Specify a connection URI that contains credentials and, if required, an inbound ID.
- Specify the contract as "InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE" to poll the MS_SAMPLE_EMPLOYEE interface table.
```
// Add service endpoint: be sure to specify InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE as the contract
Uri ConnectionUri = new Uri("oracleebs://ebs_instance_name");
serviceHost.AddServiceEndpoint("InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE", binding, ConnectionUri);
```
To receive polling data, open the service host. The adapter will return data whenever the query returns a result set.
```
// Open the service host to begin polling
serviceHost.Open();
```
To terminate polling, close the service host.

Important

The adapter will continue to poll until the service host is closed.
```
serviceHost.Close();
```

Example

The following example shows a polling application that polls the MS_SAMPLE_EMPLOYEE interface table. The PollingInput property contains the select statement that reads all the data from the MS_SAMPLE_EMPLOYEE table and the post poll statement deletes all the data from the same table. The first polling message gives all the records from the MS_SAMPLE_EMPLOYEE interface table. Subsequent polling messages will not contain any records because the post poll statement deletes the records. Until more data is added to the MS_SAMPLE_EMPLOYEE interface table, you will not get any polling messages. So, you must repopulate the MS_SAMPLE_EMPLOYEE interface table with new records. You can do so by running the insert_apps_data.sql script provided with the samples. After you run this script, the next polling operation will fetch the new records inserted into the table. The adapter will continue to poll until you close the service host by pressing <RETURN>.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Adapters.OracleEBS;
using Microsoft.ServiceModel.Channels;
using System.ServiceModel;
using System.ServiceModel.Description;
using System.ServiceModel.Channels;
using System.Collections.ObjectModel;

namespace SelectPolling_ServiceModel
{
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

    public class PollingService : OracleEBSBindingNamespace.OracleEBSBindingService
    {
        public override void Poll(Poll request)
        {
            Console.WriteLine("\nNew Polling Records Received");
            Console.WriteLine("*************************************************");
            Console.WriteLine("Emp No\tName\tDesignation\tSalary");
            for (int i = 0; i < request.DATA.Length; i++)
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}",
                request.DATA[i].EMP_NO,
                request.DATA[i].NAME,
                request.DATA[i].DESIGNATION,
                request.DATA[i].SALARY);
            }
            Console.WriteLine("*************************************************");
            Console.WriteLine("\nHit <RETURN> to stop polling");
        }
    }

    class PollingCredentials : ClientCredentials, IServiceBehavior
    {
        public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
        {
            bindingParameters.Add(this);
        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        protected override ClientCredentials CloneCore()
        {
            ClientCredentials clone = new PollingCredentials();
            clone.UserName.UserName = this.UserName.UserName;
            clone.UserName.Password = this.UserName.Password;
            return clone;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            ServiceHost serviceHost = null;
            try
            {
                Console.WriteLine("Sample started...");
                Console.WriteLine("Press any key to start polling...");
                Console.ReadLine();

                OracleEBSBinding binding = new OracleEBSBinding();
                binding.InboundOperationType = InboundOperation.Polling;
                binding.PolledDataAvailableStatement = "SELECT COUNT (*) FROM MS_SAMPLE_EMPLOYEE";
                binding.PollingInput = "SELECT * FROM MS_SAMPLE_EMPLOYEE FOR UPDATE";
                binding.PollingAction = "InterfaceTables/Poll/FND/APPS/MS_SAMPLE_EMPLOYEE";
                binding.PostPollStatement = "DELETE FROM MS_SAMPLE_EMPLOYEE";
                binding.OracleUserName = "myOracleEBSUserName";
                binding.OraclePassword = "myOracleEBSPassword";
                binding.OracleEBSResponsibilityName = "myOracleEBSResponsibility";

                // This URI is used to specify the address for the ServiceEndpoint
                // It must contain the InboundId that was used to generate
                // the WCF service callback interface
                Uri ConnectionUri = new Uri("oracleebs://ebs_instance_name");

                // This URI is used to initialize the ServiceHost. It cannot contain
                // an InboundID; otherwise,an exception is thrown when
                // the ServiceHost is initialized.
                Uri[] baseUri = new Uri[] { new Uri("oracleebs://ebs_instance_name") };

                PollingCredentials credentials = new PollingCredentials();
                credentials.UserName.UserName = "<Enter user name here>";
                credentials.UserName.Password = "<Enter password here>";

                Console.WriteLine("Opening service host...");
                PollingService service = new PollingService();
                serviceHost = new ServiceHost(service, baseUri);
                serviceHost.Description.Behaviors.Add(credentials);
                serviceHost.AddServiceEndpoint("InterfaceTables_FND_APPS_MS_SAMPLE_EMPLOYEE", binding, ConnectionUri);
                serviceHost.Open();
                Console.WriteLine("Service host opened...");
                Console.WriteLine("Polling started...");
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception :" + e.Message);
                Console.ReadLine();

                /* If there is an error it will be specified in the inner exception */
                if (e.InnerException != null)
                {
                    Console.WriteLine("InnerException: " + e.InnerException.Message);
                    Console.ReadLine();
                }
            }
            finally
            {
                // IMPORTANT: you must close the ServiceHost to stop polling
                if (serviceHost.State == CommunicationState.Opened)
                    serviceHost.Close();
                else
                    serviceHost.Abort();
            }
        }
    }
}

Share via