Table Operation on Oracle 11g XE with OracleDbBinding

Last year I did a post on table operations on SQL Server Table with WCF-SQL Adapter from BizTalk on SQL. Now I like to do similar thing on table in Oracle 11g Express database using the Oracle Binding from BizTalk Adapter Pack 2010. Oracle 11g Express Edition is available through OTN. For downloading software you will need to have an account!

It is important that when accessing Oracle you have appropriate client installed. This can sometimes be a hassle if you do not know, which client you need. When you trying to connect to Oracle you will get error messages like:

Connecting to the LOB system has failed.

Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified..

This can be pretty annoying, but there is documentation that can help you going the right direction. In installation documentation I read that supported versions are:

• Oracle database version 11.1,
• Oracle database version 10.2,
• Oracle database version 10.1,
• Oracle database version 9.2

And supported client versions:

• Oracle Data Access Components for Oracle Client 11.1.0.6 with Patch Set 11.1.0.7,
• Oracle Data Access Components for Oracle Client 11.1.0.7

Installing Oracle 11g Client Release 2

I basically need the Oracle 11g client and I downloaded the latest release 2 version. I installed the Oracle 11g XE and installed the 11g client release 2. The following steps describe the process of installation. First click setup in client folder after having unzipped the win32_11gR2_client.rar file.

Select InstantClient and click Next.

Select location to install the client and click next.

You’ll be presented a summary of selected options. You can click finish.

Installation of Oracle client will start.

When installation is finished you can click close. To verify if the Oracle.Database Assembly is present view the assembly cache.

As you can see I installed the x86 client and have the appropriate assemblies version 2.112.2.0.

BizTalk Project and generate schema (‘s)

First, we need the XML schemas, which represent our data source. With proper installation of BizTalk Server 2010 Adapter Pack (see my post on its installation), you will have a metadata browser built into Visual Studio. NET at your disposal. Create a visual studio BizTalk project. 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.

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

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.

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.

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

Create message instances

Next step is to generate messages from generated schema, which can be routed from Recieve location in Receive Port to a send port with WCF-OracleDb Adapter (messaging based solution). Generating the messages is easy, by opening first OracleDBBindingHR.Table.EMPLOYEES.xsd in file-editor you can move the desired operation to top, so 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 TableOperations, 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">
<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">
<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">
<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">
<ns0:FILTER>FILTER_0</ns0:FILTER>
</ns0:Delete>

Build and deploy the BizTalk project

Only schemas are required for a messaging only scenario with WCF-Oracle Adapter to perform operations on Oracle 11g XE database. Therefore after assigning a strong name and application name to BizTalk project it can be build and deployed.

Configure the application

After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-Oracle Send Port. Through BizTalk Management Console one can browse to OracleTableOperations application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_OracleDBBinding_Custom.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically.

If you look at created send port and configuration you will notice that everything is there. Although having all operations in one action mapping is not going to work. If you try to send message with select statement to BizTalk you will get following error:

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.

Reason that this error appears is that an action header consisting of four actions is not clear to system. It cannot determine which one the appropriate one was. So taking it down to a one line/action declaration corrected the problem.I had to create three similar send ports based on first generated send port. Other step is setting filters on WCF-Send Port and File Send Port. Configuration is as follows:

Port Name	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

Next error I ran into was the following, when I tried testing with SelectStatement message:

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.MetadataException: Metadata resolution failed for OperationId: "http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES/Select". ---> Microsoft.ServiceModel.Channels.Common.ConnectionException: Due to an Oracle Client limitation, the adapter failed to open a connection. This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters, or (b) ambient transaction is present and a non-TNS based URI was used. To resolve this, use a TNS alias to connect to Oracle and make sure it is not more than 39 characters.

To resolve this error I had to change the following properties in the oracleDBbinding:

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

I also like to note that format of filter for instance in select statement has to be correct or you will run into this error:

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

My first message looked like:

<Select xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
<COLUMN_NAMES>*</COLUMN_NAMES>
<FILTER>where LAST_NAME='King'</FILTER>
</Select>

As I assumed the syntax would be similar to WCF-SQL, but that was not the case. It should be:

<Select xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
<COLUMN_NAMES>*</COLUMN_NAMES>
<FILTER>LAST_NAME='King'</FILTER>
</Select>

Outcome of this message the following:

And in Oracle there are two employees with last name King.

Next thing I tried was an update using following statement:

<Update xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
<RECORDSET>
<EMAIL>JAKING</EMAIL>
</RECORDSET>
<FILTER>EMPLOYEE_ID=156</FILTER>
</Update>

Which resulted in following response:

To insert new employee I used the following message:

<Insert xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
<RECORDSET>
<EMPLOYEESRECORDINSERT>
<EMPLOYEE_ID>207</EMPLOYEE_ID>
<FIRST_NAME>Steef-Jan</FIRST_NAME>
<LAST_NAME>Wiggers</LAST_NAME>
<EMAIL>SWIGGERS</EMAIL>
<PHONE_NUMBER>0123456789</PHONE_NUMBER>
<HIRE_DATE>1999-05-31</HIRE_DATE>
<JOB_ID>IT_PROG</JOB_ID>
<SALARY>100000</SALARY>
<COMMISSION_PCT>0.35</COMMISSION_PCT>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>60</DEPARTMENT_ID>
</EMPLOYEESRECORDINSERT>
</RECORDSET>
</Insert>

And response message is similar to update.

Finally I deleted the previous inserted employee with delete statement:

<Delete xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
<FILTER>EMPLOYEE_ID=207</FILTER>
</Delete>

The response was:

As result no data was found. As you can see it looks pretty straight forward, but takes some effort to setup things. You can find other Oracle sample for BizTalk 2010 Adapter pack here.

Technorati: biztalk server 2010