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:
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?
ORA-01008 not all variables bound. Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed. Action: In OCI, use an OBIND or OBINDN call to substitute the required values.
You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example :ret_val := 1; To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example. SQL> begin 2 :ret_val:=4; 3 end; 4 / PL/SQL procedure successfully completed.
REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This allows you to store SELECT statements in the database and reference them from SQL*Plus. A REFCURSOR bind variable can also be returned from a stored function.
I know this is an old question, but it hasn't been correctly addressed, so I'm answering it for others who may run into this problem.
By default Oracle's ODP.net binds variables by position, and treats each position as a new variable.
Treating each copy as a different variable and setting it's value multiple times is a workaround and a pain, as furman87 mentioned, and could lead to bugs, if you are trying to rewrite the query and move things around.
The correct way is to set the BindByName property of OracleCommand to true as below:
var cmd = new OracleCommand(cmdtxt, conn); cmd.BindByName = true;
You could also create a new class to encapsulate OracleCommand setting the BindByName to true on instantiation, so you don't have to set the value each time. This is discussed in this post
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With