Sunday, April 17, 2011

Fun with the BizTalk 2010 Oracle E-Business Suite Adapter

I recently had an opportunity to use the BizTalk 2010 Oracle E-Business Suite WCF adapter, and thought I'd share some useful tips for getting it up and running and connected to an EBS instance.

We'd already installed the WCF adapters that come as part of the "Install BizTalk Adapters" (aka Adapter Pack) in the BizTalk installation media.

Next thing was that we needed the Oracle database client installed on the BizTalk dev machine and to be able to establish connections to Oracle databases. We were using Windows Server 2008 R2 x64 for our development OS, but had actually had quite a bit of trouble first locating and then getting the x64 Oracle client to work with BizTalk. It was fine from Visual Studio as it's a 32-bit application and uses the 32-bit Oracle client, but as soon as we attempted to interact with Oracle from the BizTalk runtime via a 64-bit host, no dice.

In the end we created a 32-bit BizTalk host and configured the Oracle adapters (Database and EBS) to use the 32-bit host for their receive & send handlers. This saved us wasting a lot more time attempting to get x64 Oracle client to work... unfortunately, there doesn't seem to be a heap of good documentation on this scenario anywhere.

Once we'd overcome that hurdle, the next was in establishing a connection to E-Business Suite itself. The documentation for the EBS adapter is VERY good, but there are a few things that can be a bit confusing when you're starting out.

First of all, when you're reading the documentation that comes with the adapter, make sure you know the "model" that the section you're reading relates to: the adapter can be used as a pure WCF adapter (ie, straight from .NET) or from BizTalk... there are some great tutorials in the documentation, but if you're trying to work with the adapter from BizTalk, the tutorials on getting it to work from .NET probably are going to be slightly less relevant and possibly even more confusing.

The next point of confusion is configuring the adapter binding itself. There are a host of properties to configure, and I'll list the key properties we used below. In particular though, note the fact that you need to specify 2 different sets of credentials when configuring the adapter: a set of credentials to connect to the Oracle database underlying E-Business Suite, and then a set of credentials for connecting to the E-Business Suite application itself.

The key properties we configured were:

Binding:
  • Application Short Name - For example, "SQLGL". You can identify the Application Short Name from the Action property of the binding that is generated within Visual Studio - something that looks like eg "InterfaceTables/Select/SQLGL/GL/GL_INTERFACE".
  • Client Credential Type - Identifies the type of credentials you're going to be using & where you'll specify them.
  • Oracle E-Business Suite Organization ID - Identifies the Organization you're interacting with in E-Business Suite. This will be a number, something like "83".
  • Oracle E-Business Suite Responsibility Key - Identifies the responsibility the E-Business Suite user has to interact with the application identified by Application Short Name for the Oracle E-Business Suite Organization ID. Will be something like "1_GL_SUPER_XXX".
  • Oracle Password - Password for the credential type NOT specified by the binding Client Credential Type property.
  • Oracle Username - Username for the credential type NOT specified by the binding Client Credential Type property.
  • Use Ambient Transaction - In our case set to False, as we didn't want our interactions with E-Business Suite to be wrapped in an encompassing transaction.
  • Enable BizTalk Compatibility Mode - Set to True in our case because we were using the adapter from BizTalk.
Credentials:
  • Username - Username for the credential type specified by the binding Client Credential Type property.
  • Password - Password for the credential type specified by the binding Client Credential Type property.
Your E-Business Suite administrator should be able to assist you with locating the correct values for each of these properties, but the 2 most important things to note are:
  • You need to enter credentials into the binding Oracle Password / Oracle Username properties and also into the credentials Username / Password properties. If you set the binding Client Credential Type property to "Database", then the Oracle Password / Oracle Username binding properties should be set to the E-Business Suite application-level credentials, and the credentials Username / Password properties should be set to the Oracle database-level credentials; if you set this property to "EBusiness", the Oracle Password / Oracle Username binding properties should be set to the Oracle database-level credentials, and the credentials Username / Password properties should be set to the E-Business Suite application-level credentials.
  • The specific credentials you choose depend on the values you specify for the Application Short Name, Oracle E-Business Suite Organization ID and Oracle E-Business Suite Responsibility Key properties: the E-Business Suite user you choose must have the responsibility identified by the Oracle E-Business Suite Responsibility Key property in the organization identified by the the Oracle E-Business Suite Organization ID property for the application identified by Application Short Name property.
Once we'd actually configured the adapter correctly, using it was very straight-forward, and the adapter documentation provides some great examples of each scenario.

HTH!

4 comments:

  1. hi,

    Did you run into installation error when trying to install both the 32-bit and 64-bit version of the Oracle client on Win2008R2? We tried installing 32-bit client first, but the 64-bit installer installation encounters failure. If you're able to install both client, then please advise what steps you took to complete the installation. Thanks.

    ReplyDelete
  2. Hi there, I'm sorry to say we had very limited success with the 64-bit Oracle client, and eventually decided to cut our losses and just go with the 32-bit client. In our case there wasn't any particular benefit we achieved by using the 64-bit client, and we were just burning time fruitlessly trying to get it to work. Unfortunately I haven't come across any bullet-proof documentation of how to get the 64-bit client and Oracle adapter working happily together. Sorry I couldn't help more, but thanks for checking out my blog, hopefully I can help next time!

    ReplyDelete
  3. Hi Dude,

    BizTalk Adapter for Oracle e-business suite is a development and run-time environment for line-of-business process management and automation that enables you to reuse your existing Oracle e-business suite procedures and applications with other applications.

    Thanks a lot.

    ReplyDelete
  4. hi Dave,

    Thanks for the article, very helpful indeed.
    But could you explain to me when do we use what with this adapter?

    For instance, in which scenario do we use Interface tables? can you explain it to me just in three lines?

    And in which case do we use the other Db calls?

    ReplyDelete