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.

1. Install InstantClient

Select InstantClient and click Next.

2. Install InstantClient Location

Select location to install the client and click next.

3. Installation InstantClient Summary

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

4. Installation InstantClient Install Product

Installation of Oracle client will start.

5. Installation InstantClient Install Finish

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

6. Oracle Client GAC

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.

image

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.

7. Configure Adapter

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.

image

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.

image

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.

image

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.

image

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.

image

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:

image

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

image

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:

image

image

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.

image

image

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:

image

image

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:

Comments

Anonymous said…
How we can call a stored procedure instead of Insert/Update operations?
Hi,

For you convenience I have created a post on how to do it.

Cheers,

Steef-Jan
Shadab said…
Hi,

in schema you are having "ns0" with all the tag, how its got removed in input xml.

my application is geting Dehydrated.
shadab said…
Hi,

in schema you are having "ns0" with all the tag, how its got removed in input xml.

my application is geting Dehydrated.
shadab said…
geting below error, and geting Dehydrated...

The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HCIS38ENG.Table.BIZ_TEST_Custom" with URL "oracledb://HCIS". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Invalid argument:




---> Microsoft.ServiceModel.Channels.Common.MetadataException: Invalid argument:





at Microsoft.Adapters.OracleDB.AbsoluteNameParser..ctor(String absoluteName)
at Microsoft.Adapters.OracleDB.OracleDBMetadataContract.AdapterResolveOperationMetadata(String absoluteName, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)
at Microsoft.Adapters.AdapterUtilities.AdapterMetadataContract.ResolveOperationMetadata(String operationId, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)
at Microsoft.ServiceModel.Channels.Common.Design.MetadataCache.GetOperationMetadata(String uniqueId, Guid clientId, TimeSpan timeout)
at Microsoft.ServiceModel.Channels.Common.MetadataLookup.GetOperationDefinitionFromInputMessageAction(String action, TimeSpan timeout)
at Microsoft.Adapters.AdapterUtilities.AdapterXmlWriter..ctor(Message indigoMessage, MetadataLookup dictionary, TimeSpan timeout)
--- End of inner exception stack trace ---

Server stack trace:
at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
You can download code from gallery (http://code.msdn.microsoft.com/Sample-Table-Operations-on-ec64d9b8). See if that is similar to your application.
shadab said…
Dear steef,

my all the code and stapes are fine, still the same issue. Pls guide me...


The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HCIS38ENG.Table.BIZ_Custom" with URL "oracledb://HCIS_TEST". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Invalid argument:

---> Microsoft.ServiceModel.Channels.Common.MetadataException: Invalid argument:


at Microsoft.Adapters.OracleDB.AbsoluteNameParser..ctor(String absoluteName)
at Microsoft.Adapters.OracleDB.OracleDBMetadataContract.AdapterResolveOperationMetadata(String absoluteName, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)
at Microsoft.Adapters.AdapterUtilities.AdapterMetadataContract.ResolveOperationMetadata(String operationId, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)
at Microsoft.ServiceModel.Channels.Common.Design.MetadataCache.GetOperationMetadata(String uniqueId, Guid clientId, TimeSpan timeout)
at Microsoft.ServiceModel.Channels.Common.MetadataLookup.GetOperationDefinitionFromInputMessageAction(String action, TimeSpan timeout)
at Microsoft.Adapters.AdapterUtilities.AdapterXmlWriter..ctor(Message indigoMessage, MetadataLookup dictionary, TimeSpan timeout)
--- End of inner exception stack trace ---

Server stack trace:
at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".
Hi Shadab,

How is connectivity with Oracle? What Oracle version are you using? Which Adapter Pack? and what Oracle client?

Cheers,

Steef-Jan
shadab said…
Hi,

I am using Biztalk 2006 R2 and oracle SQL *Net Client (V.10.2.0) with Microsoft BizTalk Adapter 3.0 for Oracle Database (Oracle Database adapter) and ODAC1020221

I tried table operation with select also, facing the same problem.

In other project: I am using polling using oracle view, which is working fine...

Thanks
Shadab
shadab said…
it seems, there is some issue with ActionMapping, Pls guide me what will be Operation Name, Action...

it will be same which is generated by oracle or we need to change....
Keep action mapping as is. This generated in Custom-WCF port. With messaging you can only have one action mapping for instance Insert. If still doesn't work I will try the sample on my 2006 R2 VM.
shadab said…
still its not working, if u can ping me on my email shadab.hasan@hp.com, i will send u code with xml...
shadab said…
Hi,

Now I am able to solve this issue and resolution is here.
I upgraded my Oracle client, LOB SDK and Adapter Pack.

My earlier environment of Development Box:

Biztalk Server 2006 R2
Oracle client 10.2
ODT with ODAC1020221
WCF LOB Adapter SDK SP1 x86 (SP1)
Microsoft BizTalk Adapter Pack x86 (1.0)


My new environment of Development Box:

Biztalk Server 2006 R2
Oracle client 11.2
ODT with ODAC1110720
WCF LOB Adapter SDK SP2 x86
Microsoft BizTalk Adapter Pack 2_0 Evaluation x86


Problem with previous environment:

1. I was not gating useAmbientTransaction property in the oracleDBbinding to make it False, by default its True with earlier Adapter pack.

2. When you import the buinding which is generated by oracle, it gives you SOAP Action header like below given, So taking it down to a one line/action




Only action to be there in action heder for single action. http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES/Select
venublogs said…
Hi,

I am trying to select/insert into oracle using this adapter without using any orchestration (simple messaging) just like you. In the port properties, am getting error in tag in the send port. Even I gave only one action like SELECT. Error is showing as: MetadataException - Argument .... is invalid.

I removed this tag and simply gave the value present in ACTION tag. Its working !! I am using 2006R2.

Thanks
Venu
venublogs said…
Hi Steef,

Send ports in the sample have decalaration like this :





but I could not resolve the issue same as Shadab. I mentioned just like this replacing the tag:

http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES/Insert

Now it worked fine for me. I used BTS2006R2 on 32-bit machine.
Anonymous said…
Hi,
I am having this issues...

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

In your post you mention this error and talk about having the correct version of the oracle client install yet you are installing 2.112.2.0 which is not the correct version according to this error message. My question is how does this work for you? I am having the same issue and have the same versions you installed. Did you have to do something to get around this error that you are not mentioning??

Thanks.
Hi,

I have the same issue when trying to access Oracle 11g XE using Windows Azure EAI CTP or through AppFabric Connect for Services. It seems that the installed version installs a higher version of the Oracle.DataAccess assembly namely 2.112.2.0, which is a higher version number than 2.111.7.0. However is seems that it specifically requires 2.111.7.0. For table operations the 2.112.2.0 is fine, but in other scenario's with for instance the cloud is not. Apparently the 2.111.7.0 needs to be in GAC and in registered in machine.config.
It seems that Oracle 11g R2 Client is not supported. The 2.111.7.0 Oracle.DataAccess.dll is part of the release 1. That is like I said in the post in client 11.1.0.6 and/or 11.1.0.7. I did install R2 and supported the basic operations on Oracle in my VM.
Anonymous said…
So you installed both 11.1 and 11.2?? I guess my question is if you installed 11.2 how did you get past the error mentioned at the top of your post? I think its rediculous to force one to back level their Oracle client to a version that is a year old and doesn't have 64bit support. I plan on opening a ticket to MS support this morning to get their take on this. Something doesn't seem right here. If I hear anything different I will update later.

Popular posts from this blog

DTAP Strategy: Pricing and Licensing

Integration, the community blasting into 2017!