SQL Oracle "ORA-01008: not all variables bound" 错误带参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1422032/
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
Oracle "ORA-01008: not all variables bound" Error w/ Parameters
提问by John
This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error.
这是我第一次与 Oracle 打交道，我很难理解为什么会收到此错误。
I'm using Oracle's ODT.NET w/ C# with the following code in a query's where clause:
我在查询的 where 子句中使用带有 C# 的 Oracle 的 ODT.NET 和以下代码：
WHERE table.Variable1 = :VarA
AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%')
AND (:VarC IS NULL OR table.Variable3 LIKE :VarC || '%')
and I'm adding the parameter values like so:
我正在添加参数值，如下所示：
cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
When I run this query, the server returns:
当我运行此查询时，服务器返回：
ORA-01008: not all variables bound
If I comment out either of the 'AND (....' lines, the query completes successfully.
如果我注释掉任何一个 'AND (....' 行，查询就会成功完成。
Why would the query run through alright if I'm only querying with two parameters, but not with three? The error I'm receiving doesn't even make sense
如果我只使用两个参数而不是三个参数进行查询，为什么查询会运行正常？我收到的错误甚至没有意义
回答by Christian13467
The ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name. Set property BindByNameto true. Than you can dismiss the double definition of parameters.
oracle 的 ODP.Net 提供程序默认使用按位置绑定。将行为更改为按名称绑定。将属性BindByName设置为 true。比您可以忽略参数的双重定义。
using(OracleCommand cmd = con.CreateCommand()) {
...
cmd.BindByName = true;
...
}
回答by Tony Andrews
It seems daft, but I think when you use the same bind variable twice you have to set it twice:
这看起来很愚蠢，但我认为当您两次使用相同的绑定变量时，您必须设置两次：
cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
cmd.Parameters.Add("VarC", "1234");
Certainly that's true with Native Dynamic SQL in PL/SQL:
当然，PL/SQL 中的 Native Dynamic SQL 也是如此：
SQL> begin
2 execute immediate 'select * from emp where ename=:name and ename=:name'
3 using 'KING';
4 end;
5 /
begin
*
ERROR at line 1:
ORA-01008: not all variables bound
SQL> begin
2 execute immediate 'select * from emp where ename=:name and ename=:name'
3 using 'KING', 'KING';
4 end;
5 /
PL/SQL procedure successfully completed.
回答by Hugh Jones
You might also consider removing the need for duplicated parameter names in your Sql by changing your Sql to
您还可以考虑通过将 Sql 更改为
table.Variable2 LIKE '%' || :VarB || '%'
and then getting your client to provide '%' for any value of VarB instead of null. In some ways I think this is more natural.
然后让您的客户为 VarB 的任何值提供 '%' 而不是 null。在某些方面，我认为这更自然。
You could also change the Sql to
您还可以将 Sql 更改为
table.Variable2 LIKE '%' || IfNull(:VarB, '%') || '%'
