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 18.104.22.168 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”;
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); // Add the first parameter a second time because it is used twice! LOL!
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.