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.

score 15 · Answer 1 · answered Jul 16 '14 at 13:41

15

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.

answered Jul 16 '14 at 13:41

skolima

31,963
27
115
151

Not true. read the documentation. Set Command property BindByName specifies wether bind by name or order – MatsW Jun 02 '21 at 09:11

score 9 · Answer 2 · answered Jul 17 '14 at 19:52

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.

score 0 · Answer 3 · answered Nov 29 '22 at 13:08

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
};

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

3 Answers3