oracle ORA-01008 绑定所有变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5678905/
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-01008 with all variables bound
提问by Grastveit
I'm using System.Data.OracleClientwhich does parameter-binding by name and are verifying that CommandText and Parameters are in sync:
我正在使用System.Data.OracleClientwhich 按名称进行参数绑定，并正在验证 CommandText 和 Parameters 是否同步：
public string CommandText { get; set; }
public IEnumerable<OracleParameter> Parameters { get; set; }
private void VerifyThatAllParametersAreBound()
{
var variableNames = Regex.Matches(CommandText, ":\w+")
.Cast<Match>().Select(m => m.Value).ToArray();
var parameteterNames = Parameters.Select(p => p.ParameterName).ToArray();
var unboundVariables = variableNames.Except(parameteterNames).ToArray();
if (unboundVariables.Length > 0)
{
throw new Exception("Variable in CommandText missing parameter: "
+ string.Join(", ", unboundVariables) + ".");
}
var unboundParameters = parameteterNames.Except(variableNames).ToArray();
if (unboundParameters.Length > 0)
{
throw new Exception("Parameter that is not used in CommandText: "
+ string.Join(", ", unboundParameters) + ".");
}
}
Still one query throws ORA-01008: not all variables bound. When manually inserting the parameter-values into the offending CommandText the query runs, so the CommandText and Parameters-values should be ok. I'm using : as prefix both for variables and parameternames and it's working for the other queries.
还有一个查询抛出ORA-01008: not all variables bound。当手动将参数值插入有问题的 CommandText 时，查询会运行，因此 CommandText 和 Parameters-values 应该没问题。我使用 : 作为变量和参数名的前缀，它适用于其他查询。
How can I pinpoint the cause of this exception?
如何查明此异常的原因？
回答by Grastveit
The mistake was not specifing DBNull.Value for null-values. So
错误不是为空值指定 DBNull.Value。所以
new OracleParameter(":Foo", item.Foo)
had to preplaced with
必须以
item.Foo == null
? new OracleParameter(":Foo", DBNull.Value)
: new OracleParameter(":Foo", item.Foo)
I think it was working earlier with ODT.NET without null-checks, but have not confirmed it. Apparently System.Data.OracleClientis dropping parameters with null-value.
我认为它早些时候在没有空检查的情况下与 ODT.NET 一起工作，但尚未确认。显然System.Data.OracleClient正在删除具有空值的参数。
回答by Yaro
If you pass nullas parameter value, you get "Not all variables bound" If you pass DBNull.Valueyou get runtime error somewhere in the OracleClient. To pass NULL, use string.Empty, OracleClient converts it to NULLfor any database type.
如果将null作为参数值传递，则会得到“并非所有变量都绑定” 如果传递DBNull.Value，则会在 OracleClient 的某处出现运行时错误。要传递 NULL，请使用string.Empty，OracleClient 会将其转换为任何数据库类型的NULL。
回答by Donal
If you have more than one parameter, you need to set BindByNameto true. For example:
如果您有多个参数，则需要将BindByName设置为true。例如：
OracleCommand cmd = con.CreateCommand();
cmd.BindByName = true;
cmd.Parameters.Add(new OracleParameter("parameter1", parameter1));
cmd.Parameters.Add(new OracleParameter("parameter2", parameter2));
回答by joym8
Based on above answers and comments, I made sure the following to resolve this issue:
根据上述答案和评论，我确保通过以下方式解决此问题：
- parameters are being bound in the same order they appear in the query
- parameter type is specified
If same parameter value is required more than once in the SQL, name each parameter differently in the SQL (not sure if this is required)
OracleParameter[] orclParams = new OracleParameter[] { new OracleParameter{ ParameterName = "param1", OracleDbType = OracleDbType.Varchar2, Value = "abc" }, new OracleParameter{ ParameterName = "param2", OracleDbType = OracleDbType.Varchar2, Value = "abc" }, new OracleParameter{ ParameterName = "date1", OracleDbType = OracleDbType.Date, Value = myDate } }; SomeFunction(sqlQuery, orclParams.ToList());
- 参数的绑定顺序与它们在查询中出现的顺序相同
- 指定参数类型
如果 SQL 中多次需要相同的参数值，请在 SQL 中对每个参数进行不同的命名（不确定是否需要）
OracleParameter[] orclParams = new OracleParameter[] { new OracleParameter{ ParameterName = "param1", OracleDbType = OracleDbType.Varchar2, Value = "abc" }, new OracleParameter{ ParameterName = "param2", OracleDbType = OracleDbType.Varchar2, Value = " abc" }, new OracleParameter{ ParameterName = "date1", OracleDbType = OracleDbType.Date, Value = myDate } }; SomeFunction(sqlQuery, orclParams.ToList());
回答by tbone
I believe Microsoft has deprecatedOracleClient as part of ADO.NET about 2 years ago.
我相信微软在大约 2 年前已经弃用OracleClient 作为 ADO.NET 的一部分。
You may want to consider using Oracle's data access components (ODAC odp.net). Easy to build-up (and check counts) of parameters using OracleParameter class. Setup and install docs found here. Oh, you can get into their Entity framework (and LINQ) support also (still beta i think?).
您可能需要考虑使用 Oracle 的数据访问组件 (ODAC odp.net)。使用 OracleParameter 类轻松构建（和检查计数）参数。在此处找到设置和安装文档。哦，你也可以进入他们的实体框架（和 LINQ）支持（我认为仍然是测试版？）。
Something to seriously consider anyway.
无论如何都要认真考虑。
