ORA-01008 Not All Variables Bound error

I have recently had the opportunity to work (once again) with Oracle. Specifically, I had to create a mechanism that would, based on configurable settings, update either a SQL Server or an Oracle database. In and of itself, this is not particularly challenging. Not since ADO.NET implemented a provider model using the Db... classes that are part of System.Data. The provider name can be used to generate the appropriate concrete instance of the DbConnection class and away you go.

While testing out this capability, I ran into this error when the target data source was Oracle. One would think (and I certainly did) was that I had missed out assigning one of the in-line parameters. The text associated with the error certainly gave that impression. And I was, after all, building the SQL statement on the fly. A bug in my logic could have placed a parameter into the SQL and not created a corresponding DbParameter.

But that was not the case.

Instead, it was that the value of one of my parameters (a string, as it turned out) was null. Not String.Empty, but null. And when you assign a null value to the parameter, it's as if you didn't bind anything to the parameter, the result being that when executing the query, a nice ORA-01008 exception is thrown. The correct way to do the assignment is to set the parameter value to System.DbNull value instead. It would appear that the SQL Server data provider doesn't have this issue, in that the problem only appeared against an Oracle data source. Not a particularly vexing problem, but still it's something to be aware of.

And a couple of years had passed since my last Oracle post ;)