ORA-01008：并非所有变量都绑定。他们有约束力 [英] ORA-01008: not all variables bound. They are bound
本文介绍了ORA-01008：并非所有变量都绑定。他们有约束力的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面的查询可以在Oracle SQL开发人员中工作，但是在.NET中运行时会抛出：
ORA-01008：不是全部变量绑定
我试过：
- 更改lot_priority（string或int）的.NET数据类型。
- 一个绑定变量名在查询中使用两次。这在我的
其他查询中不是一个问题，在多个
位置中使用相同的绑定变量，但只是为了确保我尝试使其第二个实例的
自己的变量具有不同的：/ />>
- 绑定变量的几种不同的方法（见注释的代码;
也是其他的）。 - 移动bindByName（）调用。
- 用文字替换每个绑定变量。我有两个单独的变量导致问题（：lot_pri和：lot_priprc）。两者之间我记不住有一些微小的变化。更改为文字使查询工作，但他们确实需要使用绑定。
<更改lot_priority（Varchar2或int32）的Oracle数据类型。
查询和代码跟随。变量名称已更改为保护无辜者：
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（ORDE R 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
A ND 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）{
字典< 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））{//查询存储在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;
//还试过下面的OracleDbType.Varchar2，并尝试将lot_pri作为整数传递
cmd.Parameters.Add（new OracleParameter（lot_pri，OracleDbType.Int32））。Value = lot_pri.ToString（） ;
/ ***********还尝试了以下更明确的代码，而不是上面的4行：**
OracleParameter param_lapp
= cmd.Parameters.Add新的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（））{
//从表中获取值（从未达到）
}
}
catch（OracleException e）{
// ORA-01008：并非所有变量都绑定
}
}
}
为什么Oracle声称并不是所有变量都绑定？
解决方案
p>我发现如何运行查询没有错误，但我犹豫称之为解决方案，而没有真正了解潜在的原因。
这更像是最开始我的实际查询：
- 评论
- 更多评论
SELECT rf.flowrow，rf .stage，rf.process，
rf.instr instnum，rf.procedure_id，rtd_history.runtime，rtd_history.waittime
FROM
（
- 子查询开头的评论
- 这两个注释行是问题
SELECT sub2.flowrow，sub2.stage，sub2.process，su b2.instr，sub2.pid
FROM（...
第二组注释以上，在子查询的开始，是问题。删除时，执行查询。其他意见也不错
这不是一些流氓或缺少换行符的问题，导致以下行被注释，因为以下行是一个SELECT。一个缺失的选择会产生一个不同于不是所有的变量绑定的错误。
我问了一下，发现一个同事遇到这个 - 引起查询的评论失败 - 几次。有没有人知道这可能是什么原因？我的理解是，DBMS对评论的第一件事是看到它们是否包含提示，如果不是，则在解析过程中删除它们。普通的评论如何不包含异常字符（只是字母和句点）会导致错误？奇怪的。
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: not all variables bound
I've tried:
- Changing the Oracle data type for lot_priority (Varchar2 or int32).
- Changing the .NET data type for lot_priority (string or int).
- One bind variable name is used twice in the query. This is not a problem in my other queries that use the same bound variable in more than one location, but just to be sure I tried making the second instance its own variable with a different :name and binding it separately.
- Several different ways of binding the variables (see commented code; also others).
- Moving the bindByName() call around.
- Replacing each bound variable with a literal. I've had two separate variables cause the problem (:lot_pri and :lot_priprc). There were some minor changes I can't remember between the two. Changing to literals made the query work, but they do need to work with binding.
Query and code follow. Variable names have been changed to protect the innocent:
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
}
}
}
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 ( ...
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."
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屋！
查看全文
