oracle ORA-00932: 不一致的数据类型：预期的 DATE 得到 NUMBER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16921702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
提问by MicroMan
I am using Oracle Data Access from .net and my query is as
我正在使用来自 .net 的 Oracle Data Access，我的查询是
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"
我收到以下错误“ORA-00932：不一致的数据类型：预期 DATE 得到 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.
提前致谢。
回答by skolima
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 Parameterscollection. Oracle Data Access pretends to bind them by name, but actually binds them by order.
此错误的最可能原因是查询中参数的顺序与您将它们添加到Parameters集合中的顺序不匹配。Oracle Data Access 假装按名称绑定它们，但实际上是按顺序绑定它们。
回答by John Pederson
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.
如果您使用的是 ODP.NET，OracleCommand 应该有一个 BindByName 属性，它会强制命令按名称而不是顺序绑定参数。
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.
与此相关有轻微的性能损失，但它可能小到在大多数情况下可以忽略不计。
