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 220.127.116.11 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 18.104.22.168.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 22.214.171.124. We installed the 126.96.36.199 version of the ODBC driver, and once again, everything looked perfect. After that, one of the DBAs suggested skipping 188.8.131.52 (due to a few noteworthy bugs) and jump to 184.108.40.206 (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 220.127.116.11 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!