Crazy Default In ODP.NET OracleCommand Causes Parameter Errors Converting From Microsoft To Oracle Provider

So you are forced to switch from the (good old reliable and processor agnostic) Microsoft provider for Oracle data access to the Oracle provider for .NET programming because Microsoft decided to ditch (depreciate) theirs in .NET 4 / Visual Studio 2010. Then you should be aware of this disturbing difference in default behavior with the current version 11g R2 11.0.2.1 of the ODP.NET (Oracle Data Provider for .NET).

The trouble lies in the main OracleCommand’s default behavior when matching query parameters to supplied parameters. They decided to use the rather strange “position sensitive” method rather than the logical “named” method. That is, each parameter added to the command will be taken as is regardless of the name, and if you reference a parameter twice in the query text then you have to add the value to the command twice too!

For example, the following command would work with the Microsoft data provider for Oracle:

using (var command = connection.CreateCommand())
{
command.CommandText = “SELECT * FROM MySchema.MyTable WHERE Column1 = :apples AND Column2 > :apples AND Column3 = :pears”;
command.Parameters.AddWithValue(“:apples”, 1);
command.Parameters.AddWithValue(“:pears”, 2);
using (var reader = command.ExecuteReader())
{

}
}

But after switching to the Oracle provider (change assembly and namespace references, use Add method instead of AddWithValue) it fails with the error “ORA-01008: not all variables bound”. This is confusing because looking at the query there should be no error, all parameters were passed and do not have NULL values. The answer is that the behavior is set to positional parameter usage. It ignores the parameter names and maps the first parameter “:apples” to the the query text part “Column1 = :apples” but the second parameter “:pears” to the second query text part “Column2 > :apples”! Hence you can imagine what could happen to your data when switching from the Microsoft to Oracle provider, especially if this was a bank balance modification or something else important like that!!!

To get this working as-is on Oracle you would have to do something silly like:


command.Parameters.Add(“:apples”, 1);
command.Parameters.Add(“:apples”, 1);     // Add the first parameter a second time because it is used twice! LOL!
command.Parameters.Add(“:pears”, 2);

But fortunately you can override this behavior by adding the following line before executing the command:

command.BindByName = true;

That sets the behavior back to (what I feel most people would expect is) the sane default.

So what does that leave us with? Well we cannot change the default so that means extra caution is required. If developers forget to add this line to all their source code where they use parameters, then they will run the risk of failure. Not a good situation. I feel this is totally un-necessary as the situations where you want positional parameter parsing is rare.

They should make it behave like the Microsoft SQL provider, where it supports both because the syntax of a named or placeholder parameter are different. For Oracle without changing their syntax they could still just decide how to match per-parameter, because named matching is obviously required when the developer called the method to add a parameter with a name string specified, falling back to positional matching when it is null or empty.

5 thoughts on “Crazy Default In ODP.NET OracleCommand Causes Parameter Errors Converting From Microsoft To Oracle Provider

  1. This bit me in the butt this week too. My update statement would execute without and error – but database would not be updated. My favorite ramification of this problem is if you’re unlucky enough to be working with fields and parameters that have similar values you could issue an update statement that could update the wrong records…

  2. Dude, you saved my life. I was ready to check myself into the mental health clinic. This gives me yet one more reason to hate Oracle, but I’m glad these mysterious swapping of params are fixed now. The LEAST oracle could do is when params are set by name, and BindByName is not set, is throw an exception like “hey, you can’t specify named params without setting our silly BindByName property.”

  3. If I hadn’t bumped into your blog I would still be searching for a solution for this freakish problem! Thanks a lot! I still taste how easy was Microsoft’s Oracle provider was, even though its deprecated compared with Oracle’s own.

Leave a Reply

Your email address will not be published. Required fields are marked *