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:
- Executes a polling statement specified for the PollingStatement binding property. The specified polling statement is a query that selects all records from the <schema name>..ACCOUNTACTIVITY table. The response for the select query is copied to a FILE location.
- Executes a post poll statement specified for the PostPollStatement binding property. The post poll PL/SQL code block moves the records in the query result set to the <schema name>..ACTIVITYHISTORY table.
- Execute the polling statement again after the timeout specified as the PollingInterval binding property.
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:
- Oracle Database 11g Expression Edition –> Getting started
- Select APEX and login with SYSTEM account
- Select a new database user, fill in APEX username and password (I used POLLING)
- Create a new workspace
- You will then login into the workspace
- Select SQL Workshop –> SQL Commands
- Here you have to paste the create_account_package commands
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:
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).
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:
- 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.
- Last thing to do is go to Binding Properties and fill in polledDataAvailabelStatement, pollingStatement and postPollStatement. This will depend on which schema name you use, so SELECT * FROM <Schema name>.ACCOUNTACTIVITY FOR UPDATE
- 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. If you select (in my case POLLING) category than you will see in Package the ACCOUNT_PKG.
- For creating polling schema we will have to change contract type to Server(Inbound Operations).
- Select POLLINGSTMT from Available categories and operations and click Add
- Click OK and schema and binding file will be created.
- Create a strong-name key file required to build and deploy the solution.
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
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.
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:
I changed useAmbientTransaction property in binding from True to False. Next I noticed a xml file in folder I was monitoring.
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.
biztalk server 2010
Comments
PolledDataAvailableStatement :
SELECT COUNT(*) FROM [MyDb].[dbo].[MyTable] WHERE status = 0;
PollingStatement:
SELECT TOP(1) [MyDataField] FROM [MyDb].[dbo].[MyTable] FOR UPDATE
WHERE status = 0 ;
PostPollStatement:
UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 1 WHERE status = 0;