05 May 2011

Oracle Adapters and BizTalk

Here's a quick list of steps to do when you need to call a Oracle stored procedure from BizTalk (Assuming you have already created the Oracle schema, tables and stored procedures):
  1. In Visual Studio, right click on your BizTalk schemas project and choose Add > Add Generated Items
  2. Choose Consume Adapter Service
  3. Select OracleDBBinding from the binding drop down
  4. Configure the URI either directly or through tnsnames.ora and enter the username/password
  5. Click on Connect. If everything is configured correctly, the category list will be populated from Oracle
  6. Under your chosen schema, select Procedure
  7. In the Available categories and operations window, select the procedure(s) you need to call and add them
  8. The Filename Prefix is the string put in front of all the generated schema files. Enter an appropriate one and click OK
  9. It will generate the xsd schemas for the request and response messages. Promote whatever fields you need now
  10. It will also generate a binding file which you can import into your BizTalk application to save yourself from having to configure that stuff manually
  11. Once you've imported the bindings, you will see a newly created send port with a name like WcfSendPort_OracleDBBinding_...
  12. Configure the WCF-Custom transport properties. Under the Credentials tab, either specify the username and password or configure Single Sign-On.
  13. Ensure the send and receive pipelines are using XmlTransmit and XMLReceive respectively.
And that is it. I'd also point out that you will need to have the WCF LOB Adapter pack installed and the WCF-OracleDB Adapter created in BizTalk Administration Console.

A common error you may encounter is something like this:

Error Description: Microsoft.ServiceModel.Channels.Common.MetadataException: Argument <BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="GET_AGGREGATE_VALUES" Action="http://Microsoft.LobServices.OracleDB/2007/03/MY_SCHEMA/Procedure/GET_AGGREGATE_VALUES" />
</BtsActionMapping> is invalid.

The reason for this is because the generated binding file created the BtsActionMapping with a default operation name (in this case GET_AGGREGATE_VALUES). But this operation name has to match the operation name used by the logical send port in the orchestration.
In BizTalk Administration Console, go back to the send port's transport properties and modify the SOAP Action header. Change the operation name to match the logical send port's operation name in your calling orchestration:

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="GetAggregates" Action="http://Microsoft.LobServices.OracleDB/2007/03/MY_SCHEMA/Procedure/GET_AGGREGATE_VALUES" />
</BtsActionMapping>

To get more visibility into communications with Oracle, you can enable listeners to log the WCF-Oracle communications by adding this to the config file:

<source name=" Microsoft.Adapters.OracleDB" switchValue="Information">
<listeners>
...
</listeners>
</source>