In this post I will give some pointers how to set up a couple of things so you are able to integrate Oracle database/Oracle eBusiness Suite with BizTalk Server 2006. The adapters can be downloaded here. To install and configure them Microsoft also released documentation. The adapter uses databases through ODBC and runs on W2K, W2K3 and XP. And Oracle client 9.2 at least is required which includes the Oracle ODBC Driver (update to driver 18.104.22.168.4). Installation of the client can be found in the documentation provided in the link above. After installation of the client, ODBC connection has to be setup. Here comes the tricky part. As soon one chooses System DSN, the name should exact ba as the TNS name i.e. mytnsname (TNS) mytnsname (DSN), see picture below.
As soon as this all behind you next thing to do is setting up a receive location to use the adapter. The following link gives you a very good explanation how to do this. At the end of this blog post a simple orchestration is shown.
I have created a slightly different orchestration, where one can place the query xml message in a receive location (FILE) like:
Query will be fired against Oracle database. The result will be received and bracket will be stripped, because they give problems with xpath queries or splitting the records in xml document into single messages using the splitter pattern. Using the log4net component one can log to database, file etcetera. Go to log4net to get that open-source project and the extension for BizTalk can be found at Scott Colestocks blog. Going through al this stuff I can now integrate easily with Oracle database/Oracle eBusiness Suite. One needs support though from an Oracle DBA to get the proper rights to schemas, where one want retrieve data from.