다음을 통해 공유

BizTalk Server Adapter Pack 2010: Oracle Table Operations

  • 아티클

Introduction

BizTalk Server Adapter Pack 2010 is a new enhanced version of the previous Adapter Pack 2.0. This pack has been updated to support .NET 4.0 Framework and Visual Studio 2010. Adapter Pack provides connectivity to the latest versions of SAP, Oracle E-Business Suite, Siebel and SQL and Oracle Databases. The WCF-based Oracle adapter supports Oracle database version 9.2, 10.1, 10.2 and  11.1. In this article, the table operation messaging scenario with Oracle 11g XE is discussed to show you the ability to connect with Oracle using the WCF-based OracleDb adapter (i.e. binding).

Scenario

The sample provided through Microsoft only shows how to select a record from Oracle database table through an orchestration. It unfortunately does not show you how to perform the other operations nor does it provide guidance to do it through a messaging way. Below you will find a diagram that shows how the operations on Oracle database table are performed in messaging solution. The rest of this article will detail how to set up this scenario yourself.

Figure 1. Diagram of the scenario

In the diagram above you see how messages are offered to BizTalk that routes them to Oracle database based on message type (namespace#root). The response is routed back to a folder. This send port has a subscription to any of the response messages. The solution only consists of schemas and ports.

BizTalk Adapter Pack 2010

To install the BizTalk Adapter Pack 2010 you will need an environment or a virtual machine with the following components installed:

  • Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4 (.NET Framework 4 is essential to use BizTalk Adapter Pack with BizTalk Server 2010. However, you can use .NET Framework 3.5 SP1 and .NET Framework 4 to write .NET applications (channel/proxy) for connecting using adapters.)
  • Microsoft Visual Studio 2010.
  • The Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK (WCF LOB Adapter SDK)2010 (present on installation media of BizTalk Server 2010).
  • Respective enterprise application clients and associated software.

With the installation you are guided through a wizard installing WCF-LOB SDK first, then the adapter pack itself and subsequently when working in a x64 environment the Adapter Pack 2010 (x64). Finally the option to install the BizTalk Adapter Pack for Enterprise Applications (non-WCF based adapters) is offered. See this post on guidance for the installation.

After the installation of the adapter pack you will not see a new adapter show up under the Adapters folder or in the drop down for Add a new Adapter. The adapter pack installs only new custom bindings. The BizTalk "adapter" for instance for the Oracle database shows up in BizTalk as the OracleDBBinding. The standard bindings  are wsHttpBinding, netTcpBinding, etc. OracleDbBinding and SqlBinding are themselves custom bindings .

Oracle 11g XE

Oracle 11g XE and its client can be obtained through Oracle Technology Network (OTN). Oracle 11g XE (Express Edition) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  It's free to develop, deploy, and distribute; fast to download; and simple to administer. Installation of Oracle 11g XE and its client is simple and straight forward.

Checking the Oracle Database Assembly

The Oracle.DataAccess assembly plays a crucial role when connecting to Oracle using the Consume Adapter Service Add-in. After installation, you can check the assembly cache and verify if the appropriate assembly version 2.112.2.0 is present. When incompatible Oracle client is installed you will the following error message when trying to connect to Oracle:

Connecting to the LOB system has failed.
 
Could not load file or assembly 'Oracle.DataAccess, Version=x.xxx.x.x, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxx' or one of its dependencies. The system cannot find the file specified.

Implementation

To implement the scenario the following tasks are required to create a message based solution for table operations on Oracle database (11g XE):

  •     Create BizTalk project and generate schemas.
  •     Create messages instances.
  •     Build and deploy the BizTalk project.
  •     Configure the application.
  •     Start the application.

BizTalk Project and Generate Schemas

First, the XML schemas are required to represent the Oracle 11g XE data source. With proper installation of BizTalk Server 2010 Adapter Pack, you will have a metadata browser built into Visual Studio. NET at your disposal. Create a visual studio BizTalk project and then you can access the metadata browser from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.

Figure 2. Consume Adapter Service Template (click to enlarge).

Click 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 to 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.

Figure 3. Configure URI, the connection string and other settings (click to enlarge).

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 sees the category browser with a set of database object categories.

Figure 4. Browse Oracle Objects.

On the root "/" character a range of un-typed generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Employee.

Figure 5. Add Table operations.

Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations. After clicking the Ok button at the bottom, schema (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET.

Figure 6. BizTalk Project layout.

The binding can be used later when the solution is deployed and the ports have to be configured for Oracle 11g Express (i.e. Send Port).

Create Message Instances

Next step is to generate the messages from generated schemas, which can be routed from Receive location in Receive Port via BizTalk to a send port with the WCF-Custom Adapter using the OracleDb binding (see the diagram in the introduction). Generating the messages is easy, by opening first OracleDBBindingHR.Table.EMPLOYEES.xsd in the file-editor so you can move the desired operation to the top. You can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for all the table operations, save to file as xml (type all files, encoding UTF-8!).

**Select
**

<ns0:Select xmlns:ns0="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
   <ns0:COLUMN_NAMES>COLUMN_NAMES_0</ns0:COLUMN_NAMES>
   <ns0:FILTER>FILTER_0</ns0:FILTER>
</ns0:Select>

**Update
**

<ns0:Update xmlns:ns0="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
   <ns0:RECORDSET>
   <ns0:EMPLOYEE_ID>70</ns0:EMPLOYEE_ID>
   <ns0:FIRST_NAME>FIRST_NAMEFIRST_NAME</ns0:FIRST_NAME>
   <ns0:LAST_NAME>LAST_NAMELAST_NAMELAST_NA</ns0:LAST_NAME>
   <ns0:EMAIL>EMAILEMAILEMAILEMAILEMAIL</ns0:EMAIL>
   <ns0:PHONE_NUMBER>PHONE_NUMBERPHONE_NU</ns0:PHONE_NUMBER>
   <ns0:HIRE_DATE>1999-05-31T13:20:00.000-05:00</ns0:HIRE_DATE>
   <ns0:JOB_ID>JOB_IDJOB_</ns0:JOB_ID>
   <ns0:SALARY>4</ns0:SALARY>
   <ns0:COMMISSION_PCT>32</ns0:COMMISSION_PCT>
   <ns0:MANAGER_ID>2</ns0:MANAGER_ID>
  <ns0:DEPARTMENT_ID>81</ns0:DEPARTMENT_ID>
   </ns0:RECORDSET>
   <ns0:FILTER>FILTER_0</ns0:FILTER>
</ns0:Update>

**Insert
**

<ns0:Insert xmlns:ns0="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
   <ns0:RECORDSET>
      <ns0:EMPLOYEESRECORDINSERT>
         <ns0:EMPLOYEE_ID InlineValue="InlineValue_0">10.4</ns0:EMPLOYEE_ID>
         <ns0:FIRST_NAME InlineValue="InlineValue_0">FIRST_NAME_0</ns0:FIRST_NAME>
         <ns0:LAST_NAME InlineValue="InlineValue_0">LAST_NAME_0</ns0:LAST_NAME>
         <ns0:EMAIL InlineValue="InlineValue_0">EMAIL_0</ns0:EMAIL>
         <ns0:PHONE_NUMBER InlineValue="InlineValue_0">PHONE_NUMBER_0</ns0:PHONE_NUMBER>
         <ns0:HIRE_DATE InlineValue="InlineValue_0">1999-05-31T13:20:00.000-05:00</ns0:HIRE_DATE>
         <ns0:JOB_ID InlineValue="InlineValue_0">JOB_ID_0</ns0:JOB_ID>
         <ns0:SALARY InlineValue="InlineValue_0">10.4</ns0:SALARY>
         <ns0:COMMISSION_PCT InlineValue="InlineValue_0">10.4</ns0:COMMISSION_PCT>
         <ns0:MANAGER_ID InlineValue="InlineValue_0">10.4</ns0:MANAGER_ID>
         <ns0:DEPARTMENT_ID InlineValue="InlineValue_0">10.4</ns0:DEPARTMENT_ID>
      </ns0:EMPLOYEESRECORDINSERT>
   </ns0:RECORDSET>
   <ns0:COLUMN_NAMES>COLUMN_NAMES_0</ns0:COLUMN_NAMES>
   <ns0:QUERY>QUERY_0</ns0:QUERY>
</ns0:Insert>

**Delete
**

<ns0:Delete xmlns:ns0="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
   <ns0:FILTER>FILTER_0</ns0:FILTER>
</ns0:Delete>

Configure the Application

Build and deploy your solution (schemas). When the deployment is successful you can start with the configuration. The FILE ports are easy to create with a receive location to let BizTalk pick up the messages. The other port that has to be created is the WCF-Custom Send Port. Through the BizTalk Management Console one can browse to OracleTableOperations application and right click the application and select Import Bindings …. Then you navigate to the file called WcfSendPort_OracleDBBinding_Custom.bindinginfo.xml, select it and click Open. You will notice that a Send Port will be created automatically.

Figure 7. Send Port Configuration (click to enlarge).

If you look at the created send port (picture above) and its configuration you will notice that everything is there. Although having all operations in one action mapping it is not going to work. If you try to send the message with for instance the select statement to BizTalk you will get following error, when BizTalk routes it to the WCF-Custom Send Port:

The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HR_Table_EMPLOYEES_Custom" with URL "oracledb://win-8bpntqktj5m:1521/XE/Dedicated". It will be retransmitted after the retry interval specified for this Send
 Port. Details:"System.InvalidOperationException: An action mapping was defined but BTS.Operation was not found in the message context.

Th reason that this error appears is that an action header consisting of four actions is not clear to the BizTalk system. It cannot determine which action is the appropriate action. So by taking it down to a one line/action declaration corrects the problem. You will need to create three similar send ports based on first generated send port to support all the operations. The next step is setting the filters on WCF-Custom Send Ports and the File Send Port. The configuration is as follows:

 Portname Type Filter
 RecvPortOracleOpsIn  FILE None
 SendPortOracleOpsOut  FILE  BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#SelectResponse
Or
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#InsertResponse
Or
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#UpdateResponse
Or
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#DeleteResponse
WcfSendPort_OracleDBBinding
_HR_Table_EMPLOYEES_Custom		 WCF-Custom BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Select
WCFSndPort_OracleDBBinding
_HR_TABLE_INSERT		 WCF-Custom BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Insert
 WCFSndPort_OracleDBBinding
_HR_TABLE_UPDATE		 WCF-Custom  BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Update
WCFSndPort_OracleDBBinding
_HR_TABLE_DELETE		 WCF-Custom BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Delete

When configuring the WCF-Custom adapter with OracleDb binding you have to set the following properties:

  • "enableBizTalkCompatibilityMode" = True (for some reasons this is set to false per default)
  • "useAmbientTransaction" = False

You might run into the following error if any of these properties are set differently:

The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HR_Table_EMPLOYEES_Custom" with URL "oracledb://win-8bpntqktj5m:1521/XE/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-00936: missing expression ---> Oracle.DataAccess.Client.OracleException: ORA-00936: missing expression

Start the Application

The final step after the configuration is to start the application (i.e. send and receive ports). You can simply test the solution by dropping one of the message instance in folder receive location is listening on. For instance you drop a message like below:

<Select xmlns="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
   <COLUMN_NAMES>*</COLUMN_NAMES>
   <FILTER>where LAST_NAME='King'</FILTER>
</Select>

In the output folder you should find a message similar as below:

Figure 8. Select Operation Message Instance (click to enlarge).

You can now try with other message instances to see how the update, insert and delete are performed on Oracle database table. The implementation detailed in this article can also be downloaded from MSDN code gallery: Sample - Table Operations on Oracle 11g XE with OracleDbBinding.

Wrap Up

As you have seen most of the work is done, when connecting with Oracle and generating the schemas and its instances. The rest is just a matter of configuration. The messaging solution will result in less latency compared to consuming data from Oracle through an orchestration. So bear that in mind. This implementation is just one of the many possible scenarios you can think of when integrating with Oracle using the OracleDb Adapter (i.e. binding). The BizTalk Adapter OracleDb binding offers support for:

  • Performing Table Operations
  • Invoking functions, functions with Record Types, or with REF CURSORs
  • Invoking Overloaded Procedures, Procedures with BFILE Parameters, Stored-Procedures
  • Polling
  • the Execute Statement

To conclude the WCF-based OracleDb adapter (i.e. binding) offers a wide range of functionality when it comes to integration with Oracle database.

See Also

Read suggested related topics:

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.