WCF-Oracle adapter for BizTalk 2009

This article will give a short and easy explanation on how to use the WCF-Oracle adapter to retrieve a result set from an Oracle database.

The first thing we need to have on the Oracle database is a stored procedure that returns an object of the type ref-cursor.
This article will give some examples on how to program them. The second thing we need to do is to install an oracle client.
This has to be done so we can add an oracle service that can connect to the database, this service will be used later on to retrieve the metadata.
Installing the client will also add the necessary oracle drivers. In this example we added the service Oracleblog with the right settings for our database.

Now we can start developing.
The first step of the development is to generate the metadata and the request-response schemas.
We start by creating a BizTalk project in Visual Studio. After having created the project, we right-click the project and choose “add generated item”. Now we choose “Consume Adapter Service” to start the wizard. This new wizard was added in Visual Studio by installing the WCF-Oracle adapter.

In the wizard we first choose OracleDBBinding for “Select a binding” then we click “configure” to configure the database settings. The first thing we do is adding the name of the service we created in the oracleclient in the field DataSourceName on the pane URI. After this we can add the required credentials. Now making the connection by clicking “connect” will show all the databases available. We have to find the right stored procedure and add this to your BizTalk project. In our example we added the stored procedure P_SEARCH.

Adding it to your project will give you two things:

The request-response schema
The binding file for your send port

In this example two schemas are generated, this is because one schema is referring to the other. Both request and response schemas are created in the same XSD-file. Now we can create a simple orchestration in which we make a mapping to the request schema and create a request-response port so we can send the request and receive the file with the record set.

Now we deploy the project to add the orchestration to the BizTalk server. The next thing we do is creating the proper send-receive port for sending to the oracle database. We do this by importing the binding file that was generated with the schemas in to our BizTalk application. This will create the send-receive port with the correct action- and database settings. We now make a file receive and send port and bind this to our orchestration.

We are now ready to send the message.

Advertisements

2 comments on “WCF-Oracle adapter for BizTalk 2009

  1. Philip Manual says:

    Thanks for a great post.
    I’m having trouble with generating schema using the ‘Consume Adapter Service’ using Visual Studio 2008 on Windows Vista/Windows 2003 R2 (both 64 bit version of OS). Not sure if its related to the version of Oracle Client that I’ve installed. Can you share the version of Oracle Client that you have installed?

    – Philip

    • Cnext says:

      Hi Philip,

      We installed the 64-bit client version 11.1.0.7.0.

      If you want to create a schema from within visual studio you need to install the 32-bit client. Therefore we first installed version 11.1.0.60 and afterwards we upgraded to 11.1.0.7.0

      Another issue we had recently is the missing of the Oracle Services For Microsoft Transaction Server on the Biztalk runtime environment. These services are not installed by default but are necessary to get it all working. You need to install the appropriate bit version. 32-bit if you want to run the adapter into a 32-bit host or the 64-bit version for 64-bit hosts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s