Poll Oracle E-Business Suite using stored procedures with the WCF service model
You can configure the Oracle E-Business adapter to receive periodic data-change messages by using stored procedures to periodically poll the Oracle database. You can specify a stored procedure as a polling statement that the adapter executes periodically to poll the Oracle database.
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 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 a 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 stored procedure you specified 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 stored procedure, you must specify the entire request message for this binding property. The request message must be the same that you send to the adapter for invoking the stored procedure as an outbound operation. 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, read the following sections.
How This Topic Demonstrates Polling
In this topic, to demonstrate how the Oracle E-Business adapter supports receiving data change messages using stored procedures, you use the GET_ACTIVITYS stored procedure to poll the ACCOUNTACTIVITY table in the Oracle database. This stored procedure is available with the ACCOUNT_PKG package. You can run the SQL scripts provided with the samples to create these objects in the database.
Note
The example in this topic polls the ACCOUNTACTIVITY table, which is a base database table created by running the scripts provided with the samples. You must perform similar procedures as described in this topic to poll any other table, including interface tables.
To demonstrate a polling operation, we do the following:
Specify a SELECT statement for the PolledDataAvailableStatement binding property to determine where the table being polled (ACCOUNTACTIVITY) has any data. In this example, you can set this binding property as:
SELECT COUNT (*) FROM ACCOUNTACTIVITYThis ensures that the adapter executes the polling statement only when the ACCOUNTACTIVITY table has some records.
Execute a stored procedure, GET_ACTIVITYS, by providing the request message as part of the PollingInput binding property. This stored procedure will retrieve all the rows in the ACCOUNTACTIVITY table and you will get a response message from the adapter.
Execute a PL/SQL block as part of the PostPollStatement binding property. This statement will move all data from ACCOUNTACTIVITY table to another table in the database. After this happens, the next time PollingInput is executed, it will not fetch any data and hence the GET_ACTIVITYS stored procedure will return an empty response message.
Until more data is added to the ACCOUNTACTIVITY table, you will continue to get empty response messages so you must repopulate the ACCOUNTACTIVITY table with new records. You can do so by running the more_activity_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 using stored procedures with the Oracle E-Business adapter with the WCF service model, you must:
Generate a WCF service contract (interface) for the stored procedure using which you are going to poll. For this example, you must generate the WCF service contract for the GET_ACTIVITYS stored procedure as an inbound operation. 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 ACCOUNTACTIVITY database table using the GET_ACTIVITYS stored procedure. A script to generate the table and the stored procedure is supplied with the samples. For more information about the samples, see Samples for the Oracle EBS adapter. A sample, StoredProcPolling_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 GET_ACTIVITYS inbound 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 GET_ACTIVITYS inbound operation.
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(Namespace="http://schemas.microsoft.com/OracleEBS/", ConfigurationName="PollingPackageApis_APPS_ACCOUNT_PKG")]
public interface PollingPackageApis_APPS_ACCOUNT_PKG {
// CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/OracleEBS/2008/05/PollingPackageApis/APPS/ACCOUNT_PKG) of message GET_ACTIVITYS
// does not match the default value (https://schemas.microsoft.com/OracleEBS/)
[System.ServiceModel.OperationContractAttribute(IsOneWay=true, Action="PollingPackageApis/APPS/ACCOUNT_PKG/GET_ACTIVITYS")]
void GET_ACTIVITYS(GET_ACTIVITYS request);
}
The Message Contracts
Following is the message contract for the GET_ACTIVITYS inbound operation.
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.MessageContractAttribute(WrapperName="GET_ACTIVITYS", WrapperNamespace="http://schemas.microsoft.com/OracleEBS/2008/05/PollingPackageApis/APPS/ACCOUNT_PK" +
"G", IsWrapped=true)]
public partial class GET_ACTIVITYS {
[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/OracleEBS/2008/05/PollingPackageApis/APPS/ACCOUNT_PK" +
"G", Order=0)]
public schemas.microsoft.com.OracleEBS._2008._05.RecordTypes.APPS.ACCOUNT_PKG.GET_ACTIVITYS.OUTRECSRecord[] OUTRECS;
public GET_ACTIVITYS() {
}
public GET_ACTIVITYS(schemas.microsoft.com.OracleEBS._2008._05.RecordTypes.APPS.ACCOUNT_PKG.GET_ACTIVITYS.OUTRECSRecord[] OUTRECS) {
this.OUTRECS = OUTRECS;
}
}
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 GET_ACTIVITYS 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 : PollingPackageApis_APPS_ACCOUNT_PKG {
// CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/OracleEBS/2008/05/PollingPackageApis/APPS/ACCOUNT_PKG) of message GET_ACTIVITYS
// does not match the default value (https://schemas.microsoft.com/OracleEBS/)
public virtual void GET_ACTIVITYS(GET_ACTIVITYS request) {
throw new System.NotImplementedException("The method or operation is not implemented.");
}
}
}
Receiving Inbound Messages For Polling Using a Stored Procedure
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 stored procedure
Use the Add Adapter Service Reference Plug-in to generate a WCF service contract (interface) and helper classes for the GET_ACTIVITYS inbound operation. 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 GET_ACTIVITYS method of this class can throw an exception to abort the polling transaction, if an error is encountered processing the data received from the GET_ACTIVITYS operation; otherwise the method does not return anything. You must attribute the WCF service class as follows:
[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]Within the GET_ACTIVITYS 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 GET_ACTIVITYS(GET_ACTIVITYS request) { Console.WriteLine("\nNew Polling Records Received"); Console.WriteLine("*************************************************"); Console.WriteLine("Tx Id\tAccount\tAmount\tProcessed"); for (int i = 0; i < request.OUTRECS.Length; i++) { Console.WriteLine("{0}\t{1}\t{2}\t{3}", request.OUTRECS[i].TID, request.OUTRECS[i].ACCOUNT, request.OUTRECS[i].AMOUNT, request.OUTRECS[i].PROCESSED); } 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 ACCOUNTACTIVITY"; binding.PollingInput = "<GET_ACTIVITYS xmlns='http://schemas.microsoft.com/OracleEBS/2008/05/PackageApis/APPS/ACCOUNT_PKG'><INRECS>OPEN ? FOR SELECT * FROM ACCOUNTACTIVITY</INRECS></GET_ACTIVITYS>"; binding.PollingAction = "PollingPackageApis/APPS/ACCOUNT_PKG/GET_ACTIVITYS"; binding.PostPollStatement = "BEGIN ACCOUNT_PKG.PROCESS_ACTIVITY(); END;";Note
Note that the value for the PollingInput binding property contains the request message for invoking the GET_ACTIVITYS stored procedure as an outbound operation.
Important
In this example, because you are polling a database table, you do not need to set the applications context. However, if you were polling an interface table, you must set the applications context by specifying the OracleUserName, OraclePassword, and OracleEBSResponsibilityName binding properties. For more information about application context, see Set application context.
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 "PollingPackageApis_APPS_ACCOUNT_PKG" to poll the MS_SAMPLE_EMPLOYEE interface table.
// Add service endpoint: be sure to specify PollingPackageApis_APPS_ACCOUNT_PKG as the contract Uri ConnectionUri = new Uri("oracleebs://ebs_instance_name"); serviceHost.AddServiceEndpoint("PollingPackageApis_APPS_ACCOUNT_PKG", 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 ACCOUNTACTIVITY database table using the GET_ACTIVITYS stored procedure. The PollingInput binding property contains the request message to invoke the GET_ACTIVITYS stored procedure that reads all the data from the ACCOUNTACTIVITY table and the post poll statement moves all the data from ACCOUNTACTIVITY to ACTIVITYHISTORY table.
The first polling message gives all the records from the ACCOUNTACTIVITY table. Subsequent polling messages will not contain any records because the post poll statement deletes the records. Until more data is added to the ACCOUNTACTIVITY table, you will not get any polling messages so you must repopulate the ACCOUNTACTIVITY table with new records. You can do so by running the more_activity_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 StoredProcPolling_ServiceModel
{
[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
public class PollingService : OracleEBSBindingNamespace.OracleEBSBindingService
{
public override void GET_ACTIVITYS(GET_ACTIVITYS request)
{
Console.WriteLine("\nNew Polling Records Received");
Console.WriteLine("*************************************************");
Console.WriteLine("Tx Id\tAccount\tAmount\tProcessed");
for (int i = 0; i < request.OUTRECS.Length; i++)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}",
request.OUTRECS[i].TID,
request.OUTRECS[i].ACCOUNT,
request.OUTRECS[i].AMOUNT,
request.OUTRECS[i].PROCESSED);
}
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 ACCOUNTACTIVITY";
binding.PollingInput = "<GET_ACTIVITYS xmlns='http://schemas.microsoft.com/OracleEBS/2008/05/PackageApis/APPS/ACCOUNT_PKG'><INRECS>OPEN ? FOR SELECT * FROM ACCOUNTACTIVITY</INRECS></GET_ACTIVITYS>";
binding.PollingAction = "PollingPackageApis/APPS/ACCOUNT_PKG/GET_ACTIVITYS";
binding.PostPollStatement = "BEGIN ACCOUNT_PKG.PROCESS_ACTIVITY(); END;";
// 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("PollingPackageApis_APPS_ACCOUNT_PKG", 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();
}
}
}
}