SQLExecute Oracle 11g XE using WCF OracleDbBinding

In previous post I detailed polling an Oracle 11g XE (Express) database using OracleDbBinding in WCF-Custom Adapter. Now I like to share how I can perform SQL statements through SQLExecute operation. You can run parameterized SQL statement on an Oracle database through WCF-Custom with OracleDbBinding. This binding supports the SQLExecute operation, which acts on an input parameter block comprised of parameter sets that enable execution of the same SQL statement once for each set. The operation will in the end return the results of the SQL statement in a generic record set.

The SQLEXECUTE operation is surfaced under the root node (/) in the Select a category pane in the Consume Adapter Service BizTalk Project Add-in. 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.
  • Click Add and the OK.

image

  • Binding file and schema’s will be generated.

image

  • You could opt to build and deploy your schema’s.

I created a BizTalk project, generated the SQLExecute schema’s and then provided the project with a name. I signed the project with strong name and deployed it. I imported the generated binding into the new BizTalk application and reviewed the configuration of Send Port that was created.

image

It is important that credentials for accessing Oracle 11g XE are present. If not you will be confronted with error depicted below.

image

You add credentials with sufficient authorization in credentials tab (I have used system).

image

Last but not least in Binding tab change the useAmbientTransaction property from True to False, otherwise you will run into a exception, see my last post.

I added a filter expression to the send port as I want to SQLExecute Operation in BizTalk completely message based; filter expression is:

BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE#SQLEXECUTE

Next a receive port and location is created. Latter is configured using FILE Adapter to pickup message with SQLEXECUTE statement. Finally a send port is created to sent response of SQLEXECUTE to file. This port has a filter on response message (type); filter expression is:

BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE#SQLEXECUTEResponse

Messaging based SQLExecute application in BizTalk is now configured and will be started. Next I copy message below to in folder, where it will be picked up and routed to send port. Message will arrive at Oracle 11g and SQLStatement will be executed the resulted will be returned to send port and routed to other send port that has subscription on response message. Response message is depicted below also.

SQLEXECSTATEMENT

image

SQLEXECSTATEMENTResponse

image

I hope this little detail of my experience with SQLExecute operation using BizTalk 2010 and its 2010 Adapter Pack can be helpful in your exploration of this operation in Oracle 11g. I have based my detail here on sample (Invoking SQL Statements Using SQLEXECUTE) you can download from BizTalk Adapter Pack 2010 samples. I did not use orchestration example provided nor the schema’s.

Comments

Post a Comment

Popular posts from this blog

Awarded MVP BizTalk Server 1st July 2010

Image
This is an exciting moment, before summer holidays will start. I have been awarded by Microsoft with 2010 Microsoft® MVP Award. I was working today and after finishing diner with the family I opened my mailbox and found the ‘ Congratulations 2010 Microsoft MVP! ’ email from Microsoft. I cannot describe how happy I am and how rewarding it feels to receive such an award. I would like to thank all people at Microsoft (ones that nominated me), community members and other involved. My wife and children for enduring the long hours without me, while I was blogging, writing or participating on forums. I would dedicated this to them. Finally I also would like to thank my former employer Inter Access (especially manager Kees Hout), dear friends Harco van Polen and Ivo Brouwer for always believing in me and supporting me for last couple of years in my career. P.S. No logo yet as I have to wait for my logo kit. Technorati: biztalk
Read more

Integration, the community blasting into 2017!

Image
In the past, now and tomorrow I will keep saying that integration is relevant, even in this day and age of digitalization. Integration professionals will play an essential role in providing connectivity between systems, devices and services. Integration drives the digitalization forward by connecting everything with anything. Integration skills are still in demand. A LinkedIn study revealed a top 10 with integration and middleware in top 5 with Cloud, Data Science, UX and Web. And the expectation for 2017 is that this will be similar. Our toolbox is expanding from on premise tooling to cloud services; we have WCF, BizTalk Server, MSMQ to Logic Apps, API Management and Service Bus. Formats ranging from flat file, EDI, XML to JSON, several protocols open and proprietary, tooling from mappers to BizTalk360. It can be challenging yet much more exciting. My conclusion is more options, more fun, at least in my view. In February I, will be travellin
Read more

Table Operation on Oracle 11g XE with OracleDbBinding

Image
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 d
Read more