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 on the BizTalk Adapter Framework (.NET Framework 2.0). These adapters appeared with BizTalk Server 2006. Later these and the SAP adapter were ported to a new Framework based on Windows Communication Foundation (WCF). This framework was called the WCF LOB Adapter SDK. The new 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 database adapters. The Oracle eBusiness, Siebel and SAP adapters are classed as 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 artifacts (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 though performing a function on Oracle HR schema using BizTalk messaging (see diagram below).


Picture 1. Scenario diagram performing a function 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).
  • The HR Schema doesn't provide a default or sample function. Therefore, you create a function in HR Schema by executing the following statement in de SQL Browser:
01.create or replace FUNCTION FN_EMPLOYEEANNUALBYEMPLOYEEID(employee_id IN NUMBER)
02.RETURN NUMBER
03.IS ANNUALPAY NUMBER(8,2);
04.BEGIN
05.SELECT SALARY * 12 "Annual_Pay"
06.INTO ANNUALPAY
07.FROM EMPLOYEES
08.WHERE rownum = 1 and EMPLOYEE_ID = employee_id;
09.RETURN(ANNUALPAY);
10.END;

Building the solution

The following steps described how to generate the schema´s necessary for performing a function 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 EMPLOYEEANNUALBYEMPLOYEEID Function in the HR Schema.
  • Specify the filename prefix is required. 
  • Click Add and the OK



Picture 2. Meta data Wizard adding a function.

  • Binding file and schema’s 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 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/Function#FN_EMPLOYEEANNUALBYEMPLOYEEID
  • 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/Function#FN_EMPLOYEEANNUALBYEMPLOYEEIDResponse

Test the solution

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



Picture 4. Request message containing the SQL statement.

  • The Function can be executed in SQL Browser of Oracle 11g XE:



Picture 5.
The Execution FN_EMPLOYEEANNUALBYEMPLOYEEID in SQL Browser.

  • Examine the response in the out folder.

 
Figure 6. EMPLOYEEANNUALBYEMPLOYEEID Function response.

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: 

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.