ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Question

I am using Oracle Data Access from .net and my query is as

command.CommandText = "select * from table1 where expirydate =:EXPIRYDATE";
command.Parameters.Add("EXPIRYDATE", OracleDbType.Date, DateTime.Today,ParameterDirection.Input);

var results = command.ExecuteScalar();

I get the following error " ORA-00932: inconsistent datatypes: expected DATE got NUMBER"

If I change my query to:

command.CommandText ="select * from table1 where expirydate =
to_date(:EXPIRYDATE,'DD/MM/YYYY')";

I get no results.

Thanks in advance.

Solution 1

Most likely reason for this error is that the order of the parameters in your query does not match the order you add them to the Parameters collection. Oracle Data Access pretends to bind them by name, but actually binds them by order.

Solution 2

If you're using ODP.NET, OracleCommand should have a BindByName property which will force the command to bind parameters by their name instead of their order.

const string sql = @"select :parameter2, :parameter1 from dual";

using (var cmd = new OracleCommand(sql, conn))
using (cmd.Parameters.Add(":parameter1", "FOO"))
using (cmd.Parameters.Add(":parameter2", "BAR"))
using (var reader = cmd.ExecuteReader()) {
    reader.Read();
    // should print "FOOBAR"
    Console.WriteLine("{0}{1}", reader.GetValue(0), reader.GetValue(1));
}

using (var cmd = new OracleCommand(sql, conn) { BindByName = true })
using (cmd.Parameters.Add(":parameter1", "FOO"))
using (cmd.Parameters.Add(":parameter2", "BAR"))
using (var reader = cmd.ExecuteReader()) {
    reader.Read();
    // should print "BARFOO"
    Console.WriteLine("{0}{1}", reader.GetValue(0), reader.GetValue(1));
}

There is a slight performance penalty associated with this, but it's probably small enough to be negligible in most contexts.

Comments

[MatsW]

Not true. read the documentation. Set Command property BindByName specifies wether bind by name or order