Santosh Benjamin's Weblog

Adventures with AppFabric, BizTalk & Clouds

The Adapter Diaries – Oracle Adapter – 1

with 4 comments


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 9.2.0.5.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 9.2.0.8 for 9i clients (I eventually found an archive on Oracle Technet with 9.2.0.5.4 reportedly available there, but that came too late as i had got into 9.2.0.8 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 9.2.0.8, this is what you see

Oracle ODBC Driver Version

 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.

2rloc-choose-the-transport.png

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.

Oracle Transport Properties

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.

Weird Oracle Adapter Error

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.

Next, set the Service name.  Now take a close look at the hint in the properties window. It says
Service Name Hint

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

System DSN

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

Manage Events - Browsing the schema

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).

NativeSQL Selection

(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=3.0.1.0, 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

Successful file dump

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.

References

Community

BizTalk Oracle Adapter Installation

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

Richard Seroter – Important Hotfixes for the Oracle adapter

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)

Microsoft BizTalk Adapter Pack Samples

Oracle

Oracle ODBC Drivers (latest) Main page

ODBC Driver 9.2.0.54 – saw this one too late – after I had installed the latest, but this doesn’t really matter unless you want this version specifically)

Advertisements

Written by santoshbenjamin

March 22, 2008 at 8:23 PM

4 Responses

Subscribe to comments with RSS.

  1. Nice writeup.

    Richard Seroter

    March 24, 2008 at 3:38 PM

  2. […] Adapter Diaries – Oracle Adapter – 2 Having successfully started with the Oracle adapter and dumped the entire table out to a file, I decided to look into the mechanisms for getting the […]

  3. Nice article. I’m working on a similar project and I’m running into a brick wall. Not with what you wrote above, I got a similar “get my feet wet” example working an hour or so before reading your article. My problem occurs when I try to receive a message and process it in an orchestration.

    Have you tried using the Oracle adapter to load data into an orchestration? For some reason I keep getting errors stating that there aren’t any subscribers and I have no idea what it is that I’m missing. I’ve created the ports, bound them to the orchestration, specified the type of message I want, etc.

    Tim

    April 10, 2008 at 8:58 PM

  4. Hello Tim,
    Actually i havent got to try the orchestrations yet. I’ve been on hols and i was going to do that next week when i get back to work. I’ll try out a sample orchestration shortly and get back to you. I was intending to follow along with the reference article in the post titled “A simple oracle adapter demo” which shows how orchestrations are used. A colleague tried orchestrations with stored procs on the recieve side a while ago and didnt have any problems.
    Rgds
    Benjy

    santoshbenjamin

    April 11, 2008 at 1:17 PM


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: