Blog

View Blog

A simple BizTalk 2006 Oracle Adapter demo

Okt 3

Written by: Wilfried Mausz
03.10.2006 13:38

During my last BizTalk workshop in Vienna I had to show how to use the Oracle database adapter. In all other BizTalk projects we have always used own C# applications or web services to read and write data from Oracle. This demo shows how to read data from an Oracle table using a SQL query with the help of the built-in Oracle DB adapter.

If you are not familiar with BizTalk-basics like creating and deploying an Orchestration or creating Receive Locations, you should have a look at a former article which shows how one can create a BizTalk workflow (http://www.cubido.at/Blog/tabid/176/EntryID/75/Default.aspx).

Installation

The Oracle adapter has to be installed separately after the installation of BizTalk server. There is an own setup for additional adapters. After the adapter was installed it has to be registered as adapter in the BizTalk Administration Console:

In the Adapters node in Plattform Settings, create a new adapter called Oracle DB which uses the formerly installed Oracle adapter.

After closing the adpater configuration dialog – when I remember it correctly – the BizTalk service has to be restarted before the new adapter can be used.

Using the adapter

At first one has to create a new Receive Port with a Receive Location which uses the Oracle adapter.

The Transport Type is set to Oracle DB (the name which was set in the adapter configuration), the used Receive Pipeline is default-XML. When opening the Address (URI) settings the transport properties of the Oracle adapter appear.

At first one has to specify the Oracle connection with service name, user and password. Then one has to configure the “Managing Events” which opens the following dialog (OracleDB Adapter uses ODBC to access the Oracle database. So to get the adapter working correctly, one has to create an ODBC connection first.):

To use a simple SQL query the event NativeSQL is chosen. Now after closing the “Managing Events” dialog the SQL statement is set (property “Poll SQL statement”) and a SQL statement which is executed after polling – for example deleting rows from an export-table or updating an exported-flag – can be set (property “Post Poll SQL Statement”).

A simple Orachestration …

For the demo I used a very simple Orchestration with just one Receive port and a Send port. Everything which is read from the Oracle adapter should be written to a file.

But before we create our fancy Orchestration, we have to add schemas for the messages created by the adapter. This is done by “Add Generated Items” to the BizTalk project. There is a selection for “Add Adapter Metadata”.

Then select the Oracle adapter and choose the formerly created Oracle receive location:

The schema wizard adds some schemas to the project. The only schema used in my demo is the “NativeSQLService_NativeSQL_x5d”

This "generic" schema is filled by the adapter. You will not get very useful field names, but the result can easily be used due to the fact that the elements in the "rowData" elements will have a certain order that can be used for parsing (for example a "Index"-functoid in a map).

I hope this article is useful to you and can save some time on getting started with the Oracle DB adapter.

Trackback Print

Location: Blogs

Wilfried's Blog

Tags:

20 comments so far...

Re: A simple BizTalk 2006 Oracle Adapter demo

Thanks for posting this demo.
A little more details about adding receive pipeline and send pipeline ..In case we are writing the data to a flat file would be of great help.

NReddy