ODP.NET Parameter Binding issue


Photo by fabio on Unsplash

Unfortunately I didn’t have anything interesting to post for quite long time. But today I want to share some interesting experience about Oracle Data Access usage in .NET.

I was using it for couple of months, but couple of days ago I’ve received a strange bug. An SQL query does not return any data. I’ve started to debug it – the query was correct with no doubt, I’ve executed it each time with different parameters in Oracle SQL Developer and it always returned correct data. But let’s take a look at the code. It’s a sample code, but it should explain the problem:

using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();

string query = @"SELECT * FROM TestTable WHERE Name = :name AND Group_guid = :groupId";

using (OracleCommand selectCommand = connection.CreateCommand())
{
selectCommand.CommandText = query;
selectCommand.Parameters.Add(new OracleParameter
{
ParameterName = "groupId",
OracleDbType = OracleDbType.Varchar2,
Direction = ParameterDirection.Input,
Value = "Test_Group_Id"
});

selectCommand.Parameters.Add(new OracleParameter
{
ParameterName = "name",
OracleDbType = OracleDbType.NVarchar2,
Direction = ParameterDirection.Input,
Value = "Test_Name"
});

using (OracleDataReader reader = selectCommand.ExecuteReader())
{
while (reader.HasRows && reader.Read())
{
// Some code here...
}
}
}
}

I was expecting, that this code should work properly, but it’s not. In my case query was a little bit more complicated, so I’ve started to analyze the query itself, simplify it, rewrite some parts, etc. At the end I’ve created a simple console application to isolate it from other parts of application. I’ve spent some time modifying it by adding and removing parameters and made a small mistake in the name of parameters. But surprisingly ODP.NET didn’t throw any exception. And then I’ve understood, that it looks like ODP.NET doesn’t support named parameters, so it’s doesn’t matter how did you name you parameters. The only thing matters – the order of the parameters in Parameters collection. So, to fix previous code sample you need to reorder Parameters.Add calls.

using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();

string query = @"SELECT * FROM TestTable WHERE Name = :name AND Group_guid = :groupId";

using (OracleCommand selectCommand = connection.CreateCommand())
{
selectCommand.CommandText = query;

selectCommand.Parameters.Add(new OracleParameter
{
ParameterName = "name",
OracleDbType = OracleDbType.NVarchar2,
Direction = ParameterDirection.Input,
Value = "Test_Name"
});
selectCommand.Parameters.Add(new OracleParameter
{
ParameterName = "groupId",
OracleDbType = OracleDbType.Varchar2,
Direction = ParameterDirection.Input,
Value = "Test_Group_Id"
});

using (OracleDataReader reader = selectCommand.ExecuteReader())
{
while (reader.HasRows && reader.Read())
{
// Some code here...
}
}
}
}

Also, there is a flag BindByName, but it’s not obvious, that it’s disabled by default (taking into account fact, that OracleParameter class has no constructor without parameterName parameter. That’s really weird. In my opinion this is an example of really bad API. It’s confusing and leads to mistakes, which hard to detect. Please, think about it each time you are creating public API.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s