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.

• Binding file and schema’s will be generated.

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

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

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

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

SQLEXECSTATEMENTResponse

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.