A few weeks ago I was merrily working away on an application that uses NHibernate to query data from an Oracle database. I didn’t feel like installing a local Oracle instance as typically I do more work against SQL Server. So I develop locally against a SQL Server database and switch to Oracle by changing a few NHibernate configuration settings. Very nice. I deploy the application into the test environment and it fails with:

ORA-12571: TNS:packet writer failure

Examining the stack trace, the error was happening in the guts of the Oracle driver. Looking up the documentation, this error typically indicates a faulty network cable or similar hardware cause. So I try another query. This time it works. I try my queries one at a time. Only one query is failing. What are the chances that the network cable only comes loose when executing a certain query? Not terribly likely, IMHO.

So I reproduced the error in a simple application and started pulling it apart. I’ll save you the blow-by-blow, but suffice it to say, the only difference between the working queries and broken query was the query parameters. (Also note that the identical code worked fine against SQL Server.) Working queries had integer parameters. The broken query had a string parameter. As soon as I realized this, I made an educated guess that it was a Unicode/ASCII conversion problem. I modified my mapping file to include the datatype in the property definition:

<property name=”InvoiceNumber” column=”invoice_num” type=”AnsiString” not-null=”true”/>

Now both Oracle and SQL Server were happy to execute the query. (N.B. Usually NHibernate can properly infer the datatype based on the property type on the class. So it is not required.)

Come on now, Oracle! Loose network cable??? Not “unable to convert Unicode to ASCII” or “unexpected datatype” or something vaguely useful. Loose network cable??? Hardware failure??? Oracle has a long way to go in improving their error messages. My database of choice is still SQL Server because, most of the time, it just works.