Polling Oracle 11g XE with OracleDbBinding

In previous post I detailed how to perform some table operations in Oracle 11g using OracleDbBinding (WCF). I now like to show how to do polling in Oracel 11g XE. You can download a number of BizTalk Server 2010 Adapter Pack samples and I will use the Polling an Oracle Database sample. What sample will do is configuring a polling transaction (query) that targets the <schema name>.ACCOUNT_PKG.ACCOUNTACTIVITY table. For each polling transaction, the following actions are performed:

You will see SCOTT in stead of <schema name> in the readme fill, but reason I am saying <schema name> here is that in Oracle 11g you need to create schema your self as there is no SCOTT. You basically have to do the following:

You will notice when trying to execute the sql script that you sometimes have to execute them one by one or leave out semicolon. In SQL Workshop Object Browser you will see the following:

image

As soon as you have executed the commands we will create an application through BizTalk Administration Console and name it PollingQuery. As soon as application is created you can import binding file belonging to solution. You will need to change value inside <ApplicationName> element to PollingQuery. When binding file is imported you can see that a receive port and send port is created. Go to receive location and double click. Go to binding tab and review in the OracleDBBindingConfigurationElement the polledDataAvailabelStatement, pollingStatement and postPollStatement (you can leave them as is).

image

Now you can open the pollingquery visual studio 2010 solution. You will notice in solution a BizTalk project with Orchestration and Schema, both can be deleted as we will create polling schema ourselves. Then following steps will lead to creation of polling schema:

image

image

image

image

The newly created binding is what we will use, and therefor the receive port in PollingQuery application will be deleted. Import the created binding in the PollingQuery application and review binding

image

As you can see the polling statement and post poll statement are based on <Schema name>.Table name or Package name (in my case schema name was Polling). In the Other tab you need to set the user account.

image

Another thing to do is adjust the Send Port (FILE Adapter require correct file location for the response) and filter expression for message it will subscribe to; in this scenario filter expression is: BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLINGSTMT#POLLINGSTMT.

Final step is starting the application and monitor the folder. For a couple minutes I saw nothing, so I checked the eventlog and found the following Error:

image

I changed useAmbientTransaction property in binding from True to False. Next I noticed a xml file in folder I was monitoring.

image

There you go polling data from Oracle 11g database using OracleDbBinding (WCF-Custom). I hope this little detail of my experience with polling data from Oracle 11g database using BizTalk 2010 and its 2010 Adapter Pack will be useful if you yourself are exploring it. If you have to work with packages in Oracle 11g using BizTalk 2010 I suggest to read this excellent post by TenaliNaga!

See Code Gallery sample for solution described in this post.

Technorati:

Labels: , ,