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

DTAP Strategy: Pricing and Licensing

Image
To implement BizTalk solutions it is a good practice to have an DTAP environment in place. DTAP stands for Development, Test, Acceptance and Production. Development and test environments are equal (mirrored) and same account for acceptance and production. Deploying DTAP environments is easy, because there are many factors influencing outcome of DTAP. One factor is pricing and licensing costs. BizTalk is available in different editions and since BizTalk depends on operating system and database there are some configuration options to. Here is an overview of possible configurations for DTAP environments, hence there is a nice BizTalk Feature Dependency Matrix. Besides features, there are non-functional requirements to reckon with like fail-over, availability and scalability. On Microsoft Pricing and Licensing FAQ section of BizTalk I found this on non functional side. In a large BizTalk Server 2006 R2 deployment, two key considerations ensure redundancy in all areas of the architect...
Read more

BizTalk Server 2010 Exam : How to prepare?

Image
In previous post I yesterday I wrote that new BizTalk 2010 exam is out. And you may figure how to prepare as in preparation material nothing is mentioned.Let’s look at what skills are measured. In a nutshell these topics are: Configuring messaging architecture (setup, manage ports, configure adapters, implement messaging solutions); Developing BizTalk Artefacts (building orchestrations, create schema’s, maps and pipelines); Debugging and Exception Handling (debug orchestrations, validate and test artefacts like schema’s, maps, pipelines); Integrating Web Services and Windows Communication Foundation (WCF) Services (WCF Adapters); Implementing Extended Capabilities (this is the BAM, BRE, RFID and EDI part); Deploying, Tracking, and Supporting a BizTalk Solution (Installation in different kinds of scenario’s, deployment of applications, bindings). You can find a lot of resources that can help in preparation through BizTalk Development Center like the BizTalk tutorials for EA...
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