Introduction

The BizTalk Adapter Pack is an add-on to the out-of the box BizTalk adapters. The first adapter pack was a wrapper around a couple of adapters Microsoft bought from iWay. The adapters for the Oracle database, Oracle eBusiness Suite, Siebel and such were rewritten based upon the BizTalk Adapter Framework (.NET Framework 2.0). These adapters appeared with BizTalk Server 2006. Later these adapters and the adapter SAP were ported to a new Framework based on Windows Communication Foundation (WCF). This framework was called the WCF LOB Adapter SDK. The newly adapters were wrapped into the BizTalk Adapter Pack 1.0 with BizTalk Server 2006 R2. Gradually these adapters were moved to 2.0 including the migration of the adapters for Oracle eBusiness Suite and Siebel from the old (non-WCF based) adapter pack. The BizTalk Adapter, still as a separate download, were packaged into the BizTalk media with the release of BizTalk 2010. From that version onwards the WCF-based adapter pack and old adapter pack (non-WCF) named BizTalk Adapters for the Enterprise applications were included with the install media found under the folder: BizTalk Accelerators.

The Oracle Database Adapter


The BizTalk Adapter pack contains a database adapter for Oracle- and SQL Server. Both are categorized as the database adapters in the adapter pack. While the Oracle eBusiness, Siebel and SAP adapters are called Line of Business (LOB) adapters. The Oracle adapter or better WCF binding enables you to expose Oracle database objects as services for instance you can expose database table operation(s) as a web service(s). The Adapter client i.e. BizTalk Server can exchange messages based on the SOAP protocol with an Oracle database. Basically the metadata of Oracle database artifacts (tables, functions, procedures, etc.) are described in the structure of a SOAP message by means of a WSDL. Through using the Metadata Wizard, a Visual Studio Plug-in installed during the installation of the Adapter SDK BizTalk (as an adapter client), you can retrieve metadata for operations. And also generate artefacts (schemas) that can be used in your integration solution. This does require a client library from Oracle i.e. ODP.NET. Therefore, to be able to browse the metadata of an Oracle database you need to install the ODP.NET library from Oracle Developer Network. This is described in the installation manual of the Adapter Pack (you can find this in <install drive>:\Program Files (x86)\Microsoft BizTalk Adapter Pack\Documents\InstallationGuide.htm).

Scenario

There are various operations supported on an Oracle database. This can be an outbound operation where BizTalk invokes and operation like:

  • Insert, Select, Update, and Delete operations on a database table.
  • Executing a stored procedure.
  • Performing a function.
  • Executing a package.
  • Insert, Select, Update, and Delete operations on a view.

This article walks you through invoking a procedure on Oracle HR schema (default or sample schema inside Oracle 11g XE) using BizTalk messaging (see diagram below).

Picture 1. Scenario diagram invoking a (stored) procedure on Oracle 11g XE.

Prerequisites

To work with the BizTalk Oracle Adapter in BizTalk Server 2013 you need to do the following:

  • Install the BizTalk Adapter Pack (you are guided during installation by a Wizard that enable you to install the Adapter Pack 32/64 bit and SDK in correct order).
  • Download and install the appropriate clients. In case you installed the 64-bit Adapter Pack then you need to install the 64-bit client (ODAC1120320_x64) and subsequently the 32-bit client (ODTwithODAC1120320_32bit). The clients can be obtained from Oracle Technology Network (OTN).

Building the solution

The following steps described how to generate the schema´s necessary for invoking a procedure against the Oracle database schema (HR). First you create a BizTalk project and sign it with a strong name and specify a name for the application. Procedure object in Oracle is surfaced under the root node (/), Schema name in the Select a category pane in the Consume Adapter Service BizTalk Project Add-in (Meta data Wizard). To be able to use this operation through orchestration or messaging in BizTalk you need to create a schema. The following steps will lead to creation of this specific schema:

  • Right click project, and then Add –> Add Generated Item –> Consume Adapter Service –> Add
  • This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the oracleDBBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style. 
  • Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories. 
  • Select root node of category pane belonging to Client (outbound operations) and you will be able to find Procedure in the available categories and operations pane. The ADD_JOB_HISTORY Procedure is an out of the box procedure belonging to HR Schema.
  • Specify the filename prefix is required. 
  • Click Add and the OK

Picture 2. Meta data Wizard adding a procedure.

  • Binding file and schemas will be generated. The binding is required later on, when solution is deployed and ports have to be configured for Oracle (i.e. a Send Port as an outbound operation is performed).

Deploy and configure the solution 

Deploy the project to the BizTalk runtime (don’t forget to sign and specify a name for the application). Then perform the following steps:

  • Import the generated binding into the BizTalk application and review the configuration of Send Port that has been created. It is important that credentials for accessing Oracle 11g XE are present in the send port. The credentials used to browse the Oracle objects through the metadata wizard are not exported. Therefore, you add credentials with sufficient authorization in credentials tab.

Picture 3. Specify credentials in runtime.

  • Create a Receive Port and location. This location a message can be dropped containing the SQL statement.
  • Add a filter expression to the send port, that invokes a procedure in the Oracle HR schema, in BizTalk to subscribe to message coming from the receive location. The filter expression will be on Message Type: BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Procedure#ADD_JOB_HISTORY
  • Create another Send Port to subscribe on the response message. Specify filter on Message Type (i.e. Response): BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Procedure#ADD_JOB_HISTORYResponse

Test the solution

  • Start the application containing both - receive and send ports.
  • Drop a message into the folder.

Picture 4. Request message containing the SQL statement.

  • The JOB_HISTORY_TABLE in HR schema has 10 records before a new job history will be added. Two records for EMPLOYEE_ID 101.

Picture 5. The JOB_HISTORY_TABLE in the HR schema.

  • Examine the response in the out folder.

Figure 6. ADD_JOB_HISTORY response.

  • Note that the response is empty with no actual information. It is more interesting to see what happened in the table.

Picture 7. The Updated JOB_HISTORY_TABLE in the HR schema.

Wrap up

This article demonstrated one of the operations that can be performed on Oracle database leveraging the BizTalk Adapter Pack that is shipped with BizTalk Server 2013. The solution in this article is completely messaging based, however you can do an implementation of this functionality through using an orchestration or simply a WCF Service.

Source Code

Sample code can be found in the MSDN code gallery: BizTalk Outbound Operations on Oracle 11g XE.

See Also

Another important place to find  an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.