ORA-01008: 并非所有变量都绑定.他们被束缚 [英] ORA-01008: not all variables bound. They are bound
问题描述
我遇到了一个 Oracle 问题，到目前为止我还没有找到原因.下面的查询适用于 Oracle SQL 开发人员，但在 .NET 中运行时会抛出:
I have come across an Oracle problem for which I have so far been unable to find the cause. The query below works in Oracle SQL developer, but when running in .NET it throws:
ORA-01008:并非所有变量都绑定
ORA-01008: not all variables bound
我试过了:
- 更改 lot_priority(Varchar2 或 int32)的 Oracle 数据类型.
- 更改 lot_priority 的 .NET 数据类型(字符串或整数).
- 一个绑定变量名称在查询中使用了两次.这不是我的问题在多个中使用相同绑定变量的其他查询位置，但只是为了确保我尝试将第二个实例设为自己的变量具有不同的 :name 并单独绑定它.
- 绑定变量的几种不同方式(见注释代码；还有其他人).
- 移动 bindByName() 调用.
- 用文字替换每个绑定变量.我有两个单独的变量导致了这个问题(:lot_pri 和:lot_priprc).两者之间有一些我不记得的细微变化.更改为文字可使查询工作，但它们确实需要使用绑定.
SELECT rf.myrow floworder, rf.stage, rf.prss,
rf.pin instnum, rf.prid, r_history.rt, r_history.wt
FROM
(
SELECT sub2.myrow, sub2.stage, sub2.prss, sub2.pin, sub2.prid
FROM (
SELECT sub.myrow, sub.stage, sub.prss, sub.pin,
sub.prid, MAX(sub.target_rn) OVER (ORDER BY sub.myrow) target_row
,sub.hflag
FROM (
WITH floc AS
(
SELECT flow.prss, flow.seq_num
FROM rpf@mydblink flow
WHERE flow.parent_p = :lapp
AND flow.prss IN (
SELECT r_priprc.prss
FROM r_priprc@mydblink r_priprc
WHERE priprc = :lot_priprc
)
AND rownum = 1
)
SELECT row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num) myrow,
rpf.stage, rpf.prss, rpf.pin,
rpf.itype, hflag,
CASE WHEN rpf.itype = 'SpecialValue'
THEN rpf.instruction
ELSE rpf.parent_p
END prid,
CASE WHEN rpf.prss = floc.prss
AND rpf.seq_num = floc.seq_num
THEN row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num)
END target_rn
FROM floc, rpf@mydblink rpf
LEFT OUTER JOIN r_priprc@mydblink pp
ON (pp.prss = rpf.prss)
WHERE pp.priprc = :lot_priprc
ORDER BY pp.seq_num, rpf.seq_num
) sub
) sub2
WHERE sub2.myrow >= sub2.target_row
AND sub2.hflag = 'true'
) rf
LEFT OUTER JOIN r_history@mydblink r_history
ON (r_history.lt = :lt
AND r_history.pri = :lot_pri
AND r_history.stage = rf.stage
AND r_history.curp = rf.prid
)
ORDER BY myrow
<小时>
public void runMyQuery(string lot_priprc, string lapp, string lt, int lot_pri) {
Dictionary<int, foo> bar = new Dictionary<int, foo>();
using(var con = new OracleConnection(connStr)) {
con.Open();
using(var cmd = new OracleCommand(sql.rtd_get_flow_for_lot, con)) { // Query stored in sql.resx
try {
cmd.BindByName = true;
cmd.Prepare();
cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2)).Value = lapp;
cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2)).Value = lot_priprc;
cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2)).Value = lt;
// Also tried OracleDbType.Varchar2 below, and tried passing lot_pri as an integer
cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Int32)).Value = lot_pri.ToString();
/*********** Also tried the following, more explicit code rather than the 4 lines above: **
OracleParameter param_lapp
= cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2));
OracleParameter param_priprc
= cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2));
OracleParameter param_lt
= cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2));
OracleParameter param_lot_pri
= cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Varchar2));
param_lapp.Value = lastProcedureStackProcedureId;
param_priprc.Value = lotPrimaryProcedure;
param_lt.Value = lotType;
param_lot_pri.Value = lotPriority.ToString();
//***************************************************************/
var reader = cmd.ExecuteReader();
while(reader.Read()) {
// Get values from table (Never reached)
}
}
catch(OracleException e) {
// ORA-01008: not all variables bound
}
}
}
<小时>
为什么 Oracle 声称不是所有的变量都是绑定的?
Why is Oracle claiming that not all variables are bound?
推荐答案
我找到了如何在不出错的情况下运行查询，但在没有真正了解根本原因的情况下，我犹豫将其称为解决方案".
I found how to run the query without error, but I hesitate to call it a "solution" without really understanding the underlying cause.
这更像我实际查询的开头:
This more closely resembles the beginning of my actual query:
-- Comment
-- More comment
SELECT rf.flowrow, rf.stage, rf.process,
rf.instr instnum, rf.procedure_id, rtd_history.runtime, rtd_history.waittime
FROM
(
-- Comment at beginning of subquery
-- These two comment lines are the problem
SELECT sub2.flowrow, sub2.stage, sub2.process, sub2.instr, sub2.pid
FROM ( ...
上面的第二组注释，在子查询的开头，是问题所在.删除后，查询将执行.其他评论没问题.这不是某些流氓或缺少换行符导致以下行被注释的问题，因为以下行是 SELECT.缺少选择会产生与并非所有变量都绑定"不同的错误.
The second set of comments above, at the beginning of the subquery, were the problem. When removed, the query executes. Other comments are fine. This is not a matter of some rogue or missing newline causing the following line to be commented, because the following line is a SELECT. A missing select would yield a different error than "not all variables bound."
我四处打听，发现一位同事遇到过这个问题——评论导致查询失败——好几次了.有谁知道这是怎么回事?我的理解是，DBMS 对注释所做的第一件事是查看它们是否包含提示，如果没有，则在解析过程中将其删除.不包含异常字符(只有字母和句点)的普通注释怎么会导致错误?奇怪.
I asked around and found one co-worker who has run into this -- comments causing query failures -- several times. Does anyone know how this can be the cause? It is my understanding that the very first thing a DBMS would do with comments is see if they contain hints, and if not, remove them during parsing. How can an ordinary comment containing no unusual characters (just letters and a period) cause an error? Bizarre.
这篇关于ORA-01008: 并非所有变量都绑定.他们被束缚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
