Table of Contents


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 and based on the BizTalk Adapter Framework (.NET Framework 2.0). These adapters appear 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 based upon what is called the WCF LOB Adapter SDK. The newly wrapped in the BizTalk Adapter Pack 1.0 with BizTalk Server 2006 R2. Gradually moved to 2.0 including migration of the adapters for Oracle eBusiness Suite and Siebel from the old (non-WCF based) adapter pack. BizTalk Adapter was subsequently packaged with the BizTalk media with BizTalk 2010 as before that the pack was a separate download. From that version onwards the WCF-based adapter pack and old adapter pack (non-WCF) named BizTalk Adapters for the Enterprise applications is included with the install media under the folder: BizTalk Accelerators.

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 pack. While the Oracle eBusiness, Siebel and SAP adapters are Line of Business (LOB) adapters. The Oracle adapter or better WCF binding enables you to expose Oracle database objects as services i.e. expose a database table operation(s) as a web service. Adapter client i.e. BizTalk Server can exchange messages based on SOAP protocol with Oracle. Basically the metadata of Oracle database artifacts (tables, functions, procedures, etc.) is describes 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) can retrieve metadata for operations and 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.

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.

Note that apart from this, the Oracle Database adapter also exposes the SQLEXECUTE outbound operation, which enables the adapter clients to execute any generic data manipulation language (DML) or stored procedure in an Oracle database. This type of operation is a parameterized SQL statement on an Oracle database. It will return the results of the SQL statement in a generic record set. This will further demonstrated in this article (see diagram below).


Picture 1. Scenario diagram.

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).

Building the Solution

The following steps described how to generate the schema´s necessary for executing a parameterized SQL statement against Oracle database. Create a BizTalk project and sign it with a strong name and specify a name for the application.

The SQLEXECUTE operation is surfaced under the root node (/) 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 SQLExecute in Available categories and operations pane.
  • Specify the filename prefix is required.
  • Click Add and the OK.


Picture 2. Meta data Wizard.
  • 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).



Picture 3. Schema's and binding.

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:

  • Next 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 4. 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 to SQLExecute Operation 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/SQLEXECUTE#SQLEXECUTE

  • 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/SQLEXECUTE#SQLEXECUTEResponse

Test the solution

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



Picture 5. Request message containing the SQL query statement.

  • Examine the response in the out folder.



Picture 6. Response message containing result of the query statement.

Wrap up

This article demonstrate one of the operation 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.

See Also

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