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.

Yorick

Answer 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.

Bowleg

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

Answer 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.

Bakunin

Answer 3

Even if you add parameters by name, if you don't explicitly tell OracleCommand that you are using bind by name, it uses bind by order / index. I got the same error "ORA-00932: inconsistent datatypes: expected DATE got NUMBER", when i set BindByName to true, it worked.

OracleCommand command = new OracleCommand()
{
    Connection = conn,
    BindByName = true
};

Penuchle