The Adapter Diaries – Oracle Adapter – 1
Well, after a quite a few months of mainly coaching and mentoring, i finally got permission to get my hands dirty with code for a new project, where it appears, that yours truly is going to be a one man army. Its quite an interesting project involving getting data from an oracle system, content enriching from some Java webservices, transformation and posting to one of those loosey goosey webservices that have no strong typing at all.
I will have quite a lot to say about the webservices soon and hopefully some interesting things to share on patterns and architecture choices, but for now i’m going to focus on the Oracle OBDC adapter (not the newfangled WCF one). I was quite excited to finally get to code again and to be able to work on an adapter that I had heard so much about but never had a chance to work with earlier.
There’s some pretty good material about the Biztalk 2006 Oracle LOB adapter and I have included some good reference articles and blog posts about this adapter, but i wanted to post my experiences with getting started with the adapter because IMO, while all the articles are good, there is nothing that takes you all the way from start to finish and there are some important things along the way that are missed out from them. Some of the stuff may be obvious to a few folk but I’m one of those who would like the whole picture laid out before me, so maybe I can do justice to that topic if you are looking for something similar. It is my intention to provide some of the missing pieces and not to replace some of the brilliant stuff out there.
A word of caution first up. When starting out with this Oracle adapter “Go Slow! Go Very Slow”. That applies to reviewing the material as well as slow when clicking through the options in the setup and configuration process.
Acquiring the adapter:
I found it surprisingly difficult to actually get my hands on the adapter. It doesnt come with the default install and all the google searches turned up various articles and posts including those about the new WCF ones but nothing as to the location. I finally found this CodeProject article on the Biztalk Oracle Adapter Installation which saved my bacon. I successfully downloaded the adapter pack and installed the Oracle Adapter. (After I had installed the Oracle client. In my case I am working with a 9i installation so although i could have used a 10g or 11g client to connect to the 9i server, I didnt want to take any chances and plumped for the 9i client).
Upgrading the driver
As soon as you install the adapter you must upgrade the Oracle ODBC driver because the one that is supplied with the Oracle client install is very old. (It is also worth noting that the adapter uses the Oracle driver and not the Microsoft Oracle driver). Now, most of the talk on the web, including the aforementioned CodeProject article indicates that you need to upgrade to the driver version 18.104.22.168.4 and mention an exe named ORA92054.exe. The CodeProject article also provides a link to get this but that link is broken. I couldnt find that version anywhere for a while so when i went to the Oracle site, i found that the latest version of the driver is 22.214.171.124 for 9i clients (I eventually found an archive on Oracle Technet with 126.96.36.199.4 reportedly available there, but that came too late as i had got into 188.8.131.52 by then). Of course if you have 10g or 11g client then the default ODBC driver supersedes even this and according to some MSDN forum posts, works just fine. Anyway, after upgrading to 184.108.40.206, this is what you see
Installing the adapter into Biztalk
This is fairly straightforward step and the MS official doc referenced below as well as the CodeProject article tell you about it. No hangups there.
Simple Scenario – Dumping a table to a file
This was my first foray into using the adapter. It follows the theme of Richard Seroters first scenario (Wiretap) in his walkthrough (referenced below), but as I mentioned earlier, there’ some basic stuff involved that Richard hasn’t covered.
Setting up the receive locations
First i created a new one way receive port and a new one way receive location using the Oracle transport.
Since I only wanted to pump out data from the table to a file when choosing the pipelines I used the pass through transmit because there was no specific document to watch for.
Gotchas in transport configuration
Note: I don’t have any probs showing you the actual names of credentials etc because this was all done on a development box that no one can hack into and besides , I find it easier to follow things when I can trace actual settings across screens whereas on some posts this info is left out which could make it difficult to follow for some readers (like myself :-)). Take a look at the Transport Properties box below and i will talk about some of the entries.
The first thing to note is the password. You cannot just enter the password in the box. You have to click the dropdown and start typing the password. I clicked the dropdown and accidentally let it slip back and an encrypted string appeared as if by magic (in hindsight that was the equivalent of a blank password).
I didn’t realise it at the time but after setting the other values and going to the Managing Events entry, I got a weird error shown in the following picture.
The next thing to note is the PATH . In some blog entries it shows “c:\oracle\ora92” . My equivalent is D drive so I set it as “d:\oracle\ora92” but that wasn’t enough and I had to include the BIN folder thanks to a prompting from this article. It may seem obvious to some, but it wasn’t to me.
That’s what you have to watch out for. I first set it to PILOT which is the service name in TNSNAMES and in Enterprise Manager, but I had set the DSN to OraclePILOT as shown below
So this threw me for a while. I could have set the DSN name to be the same as the Service name but I guess that wouldn’t have shown me what I was missing in not looking at the hints.
Anyway, after setting the SERVICE name – and setting the user name I was then able to set up the POLL statement. As you can see in the transport properties screenshot I was just referencing the good old SCOTT schema. Then on clicking the Manage Events browse button, it threw that weird error again. To get out of this I needed to click OK on the Transport Properties (after entering the credentials and service name) and then go back into the properties in order to get into the Manage Events window. Again, it may be obvious to some but not to me. Why couldn’t it save the configuration automatically when Manage Events was clicked? After all if the credentials etc are wrong its simple enough to throw the appropriate error message. (Btw, I later found more MSDN documentation regarding setting the transport properties (the link is in the references below)and it clearly says click OK after setting the user name, so i guess the principle of RTFM applies to this).
Another important point, set the Polling statement AFTER you finish with the Managing Events because you have to choose whether you want a Table Change Event (in which case Polling and Post-Polling dont apply) or whether you want NativeSQL (for which polling and post-polling must be specified). Again, i think the sequence of entries on the dialog box leaves much to be desired. It is definitely not intuitive.
Ok, so when I saved the config and then went to manage events, it showed me the browsing window but there was nothing under the service name initially (because I had set the service name to PILOT instead of OraclePILOT). Once I changed that, the window showed me the following
So then following along with the article I set the Native SQL event as shown in the following screen. Note that I just highlighted NativeSQL and added it – I didn’t go any further to low level items such as SQLEvent (at this time , I don’t know what they mean, so its best to avoid playing around too much . Maybe later a bit of experimentation will be in order).
(After this i set the Poll statement) Thats all there is to it. The rest involves setting the Biztalk application handler and the pipeline.
So I then created a send port to subscribe to the receive port (using the filter BTS.RecievePortName) and dump the message to a file and sat back.
Initially I had used the XML receive and send pipelines, but that didn’t work. All I got was an error message saying
There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=220.127.116.11, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “XML disassembler” Receive Port: “ReceivePort1” URI: “OracleDb://OraclePILOT_7e690853-1ea3-4736-adb7-5134efec6366” Reason: Finding the document specification by message type “http://schemas.microsoft.com/[OracleDb://OraclePILOT/NativeSQL]#SQLEvent” failed. Verify the schema deployed properly.
Thats one of the familiar Biztalk errors. That happens because there wasnt a subscriber for that message type as the send port was only subscribing to the receive port, not the specific message. Once I set them to pass-through on both ends, I got my first successful file dump shown below
So, thats it. Check out Richard Seroters article referenced below for some more of the use cases that he set out to prove. In my next article I’ll talk about the choice of events (TblChange vs SQLEvent) that i had to wrestle with.
Hope you found this helpful. Your feedback would be much appreciated. Here are some of the good reference materials i found.
Richard Seroter – A walk through the Biztalk 2006 Oracle adapter -This is a brilliant post from top guru Mr.Seroter. Also check out the comments to the post and his replies for some interesting discussions
A simple Biztalk 2006 Oracle Adapter demo – Another good one.
Official MS stuff
Installing and configuring Adapters for enterprise applications – also available as a downloadable whitepaper AT this link
Oracle ODBC database adapter – The whole enchilada of the Oracle adapter MSDN docs – definitely worth reading at some point 🙂 usually at the beginning of the dev effort!
Oracle Database Transport Properties Dialog Box (part of the above link but called out here as a special mention)
ODBC Driver 18.104.22.168 – saw this one too late – after I had installed the latest, but this doesn’t really matter unless you want this version specifically)