Given that my posts on BizTalk + Oracle integration are consistently my most popular, I thought I’d add another one to the mix.
The first BizTalk application that my company deployed required BizTalk to poll an Oracle 8i database. When BizTalk was first installed here, we followed the documented instructions and installed the Oracle 9.2 client, and specifically, the Oracle 22.214.171.124 version of the ODBC driver. For the most part, everything has been smooth sailing.
Recently, we took that application down for to fix a bug in the Oracle view, and while that work was being done, the underlying Oracle database was upgraded from 8i to 9i. No big deal, right? Well, after the bug had been fixed in the Oracle view, and we turned the application back on in our “test” environment, we noticed some very bizarre behavior.
After some initial investigation, I saw that the value (of type varchar2) being pulled from the Oracle database appeared to be truncated! Only seven characters (out of eight) were being pulled, and, every other value came in empty. Yowza.
So where to begin troubleshooting this? The first place we looked was the database itself. Had anything changed? Oracle 9i has significantly more Unicode support, so we checked to see if data was improperly converted from 7-bit ASCII to Unicode. The data appeared to be intact and unmolested.
Given that no changes had occurred on the BizTalk application itself, it seemed unlikely that BizTalk was the culprit. We checked a few basic things such as the “maxLength” value (identical in both BizTalk schema and Oracle view), and also confirmed that the BizTalk binding configuration had not changed.
So, next we looked at the connection to Oracle itself. Was the ODBC connection the troublemaker here? I decided to open Visual Studio.NET, and using the Server Explorer, create a connection to my Oracle database by reusing the existing Oracle ODBC DSN. Sure enough, when I viewed the Oracle view in question, I saw truncated (and missing) values! I knew positively that the underlying data was right, so the connection was the piece that was distorting my data.
I confirmed this by going to a different environment (with the Oracle 10g client installed) and using Visual Studio.NET to browse the Oracle database table. Using the 10g client, the data appeared correctly.
So, in our development environment, our administrators tried downgrading our Oracle ODBC driver to 126.96.36.199.0. When we did that, the data also appeared correctly. However, I wasn’t comfortable downgrading, and suggested moving one Oracle ODBC version past the Microsoft-recommended 188.8.131.52. We installed the 184.108.40.206 version of the ODBC driver, and once again, everything looked perfect. After that, one of the DBAs suggested skipping 220.127.116.11 (due to a few noteworthy bugs) and jump to 18.104.22.168 (with the latest DST patch).
So what the heck caused this? I still don’t have a great answer. The only change to this system was the database upgrade, but the DBAs were fairly confident that existing connections should not have been affected. The set of tubes known as the internet turned up very few results. There was some information on Oracle ODBC and Unicode, and some other gripes with the 22.214.171.124 driver, but nothing that explained why the ODBC connection to the database mangled my result set.
Moral of the story? When troubleshooting these sorts of scenarios, check off each area that could have caused the problem, and use multiple environments (with different configurations) to isolate the problem. Then when all else fails, upgrade Oracle drivers!
A little story I have with integrating with Oracle is this: After installation of Oracle client and ODBC driver update and setting up connection with adapter and so on with a customer I am working for everything went soomthly until messages with data from Oracle views started to get very large and I got out of memory exceptions. The customer IT guys deicided not to upscale existing BizTalk Environment with more memory, but set up a complete new one with 64-bits OS Windows 2003 Server. Setup BizTalk again and so on and finally configuration to setup connections with Oracle; but then Oracle client 9 and its drivers did not work, so I ended up upgrading with Oracle 10g client after a couple of hours trying to figure out why it did not work anymore. So comparing your scenario with mine I agree with your statement that when everything else fails upgrade Oracle drivers.