BizTalk 2013 and configuring the Oracle Adapter in a 64 bit environment

At my current company, I was on a recent project that needed to query master data housed in our PeopleSoft  Enterprise Resource Planning (ERP) System.  Generally, we have leveraged the Request/Reponse message pattern via web services when querying ERP master data.  However, on this project, we needed multiple succinct request messages to get the required data.

These smaller high volume messages were a definite limitation for PeopleSoft.  PeopleSoft uses Integration Broker (IB) for messaging, and we discovered early on that it's ability to respond in a timely manner to a high volume of requests was sub par.  Luckily we have BizTalk, which is interoperable, and provides an Oracle adapter that could be used to access the PeopleSoft Oracle database.  Having never used the Oracle Adapter before, I started Googling around for help.

I found two blog posts that got me rolling.   The first is installing the BizTalk LOB Adapter by Sandro Pereira which can be found here:

• BizTalk 2013 Installation and Configuration – Installing BizTalk Adapter Pack

This is a step-by-step guide with screen shots for installing the BizTalk Line of Business Adapter Pack. Although I was more interested in the Oracle adapter, I went ahead and installed the other adapters as well (SAP, Siebel, Oracle E-Business Suite, etc.).  In addition, this blog post also guides the user on how to add the adapter to the BizTalk Administration Console.

The second article, written by Jason Agostini, was around configuring the BizTalk Oracle adapter which can be found here:

• Getting off the ground with the WCF-Oracle Adapter

The only problem I had with the second article was that it was geared to a 32 bit development environment and an earlier version of the Oracle Database.  So using the above article as a guideline, here are the steps I took to get the Oracle adapter configured in a 64-bit environment:

• First I contacted our PeopleSoft Admin to get the current version of the PeopleSoft Oracle Database (11.2.0.2).  I then went to the Oracle website and downloaded the appropriate 32-bit and 64-bit ODAC zip files (ODAC112021XCopy).  Why not just the 64-bit?  For design time development, you will need the 32-bit version to create a connection to the database.
• From the readme.txt included in the ODAC zip files, I installed the assemblies by running the "install.bat" file from the command prompt.  Again, I had to do this twice, once for the 32-bit version and once for the 64-bit version.
• I then navigated to the directory where the BizTalk Adapter Pack was installed (In my case, C:\Program Files (x86)\Microsoft BizTalk Adapter Pack\bin) and opened the file "Microsoft.Adapters.OracleDB.config".  I then added an entry to reference the new version of the Oracle DataAccess assembly:

• Having completed the installation, I opened Visual Studio 2012 and brought up my BizTalk solution. In my schemas project, I right clicked the project and selected "Add Generated Items" selection:

• From the "Add Generated Schema" window, I selected the "ConsumeAdapterService" option:

• The next step is to select your binding, in my case "OracleDBBinding", and click the "Configure" button.  This is where I entered database information like ServerName and ServerAddress in the "URI Properties" tab.  Enter any credentials needed for access in the "Security" tab:

• Once the database information is entered, click the "OK" button.  From the "Configure a URI" text box, copy the text and paste into an application like notepad for later (you will need this information when setting up your send port in BizTalk Administrator).  
• Click the "Connect" button.  Once connected to the database, I selected the "Client (Outbound operations)" from the "Select Contract type" pull down list (which will allow me to pull data from the database).  Select the appropriate database object and operation from the menu panes.  Click the "Add" button so that your operation is added to the "Added categories and operations" menu pane.  Click the "OK" button :

• From this, a schema of the database object is created in the BizTalk project that can be used to retrieve data.  In my case, this was a table. Included in the schema's "Select" record is a filter element which acts as a "WHERE" clause to narrow your return set.  Below is a screenshot of a sample table schema with the "Select" and the "SelectResponse" records:

• After completing development (in my case an orchestration), building, and deploying my project, it was time to configure the send port in BizTalk Administrator.  The first step is to create a new Send Port for the application.  Right click the "Send Ports" and add new "Static Solicit-Response Send Port"
• When selecting the type of adapter to use, I have two choices.  I  can either select the "WCF-Custom" or the "Oracle" adapter:

• Since I selected the "WCF-Custom", under the "Binding" tab of the adapter I needed to select "oracleDBBinding":

• When configuring the adapter, under the "General" tab, there are two important steps.  First, I entered the Address (URI) by copying and pasting the text saved in notepad from my previous step of connecting to the Oracle database.  Second, the Soap Action should come from the "Target Namespace" of the database object schema created with the Adapter service.

• To ensure I was using the 64-bit version of the adapter, I made sure the BizTalk Host associated with the adapter was configured with the check box for "32-bit only" not selected:

• Running a test message, I was able to confirm that the send port was sending and receiving messages with the Oracle database successfully.  One thing to note, directly accessing an ERP database table is not a recommended approach, and I only used a table to get a working example up and running.