The past weeks I've been doing a lot of work to improve performance at our application at work. One of the things I've done is to replace the Microsoft's OracleClient for the ODP.NET provider. In my tests I've been getting data access times up to 3 times faster than with the OracleClient. I'll post more details on this in a latter article, for now I wanted to focus on the following error I got when switching providers:
Oracle.DataAccess.Client.OracleException: ORA-01008: not all variables bound
I found this strange since we have a data layer that abstracts the provider and we already have implementations for Oracle and SQLServer and everything was working great. It took me a while but I found out that this is was only happening in queries where I used the same parameter more than once. Here is an example:
SELECT NAME, AGE FROM EMPLOYEE WHERE :AGE > 18 AND :AGE < 40
What I found out is that the default behavior for the ODP.NET provider is different than the OracleClient provider. The ODP.NET sets the parameters (OracleParameter) on the OracleCommand using the order of the parameters instead of the name. When this happens you'd have to declare the parameter twice with the same value. This behavior can be changed using the BindByName property on the OracleCommand.
var parameter = new OracleCommand();
parameter.BindByName = true;
The good part for us is that since we had an abstraction layer on top of our data layer all we needed to do was make a small change to the Factory class that creates the commands for us. Everything else remains untouched.
Hope this helps.
Oh, and by the way... on .NET 4 many types under the System.Data.OracleClient namespace is marked as obsolete. So you'd better watch out.