title: ORA-01008: not all variables bound. They are bound
body: <p>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:</p>
  
  <blockquote>
    <p>ORA-01008: not all variables bound</p>
  </blockquote>
  
  <p>I've tried:</p>
  
  <ul>
  <li>Changing the Oracle data type for lot_priority (Varchar2 or int32).</li>
  <li>Changing the .NET data type for lot_priority (string or int).</li>
  <li>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.</li>
  <li>Several different ways of binding the variables (see commented code;
  also others).</li>
  <li>Moving the bindByName() call around.</li>
  <li>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.</li>
  </ul>
  
  <h2>Query and code follow. Variable names have been changed to protect the innocent:</h2>
  
  <pre><code>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 &gt;= 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
  </code></pre>
  
  <hr>
  
  <pre><code>public void runMyQuery(string lot_priprc, string lapp, string lt, int lot_pri) {
  Dictionary&lt;int, foo&gt; bar = new Dictionary&lt;int, foo&gt;();
  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
          }
      }
  }
  </code></pre>
  
  <hr>
  
  <p>Why is Oracle claiming that not all variables are bound?</p>