Not all variables bound oracle ошибка

Dear all,
Could you please review the following example 1. Not sure where the problem is. Isn’t supported by oracle! Thanks in advance for the help!

DB version: 10g rel2

Example 1: Does not work — Using string. Gives an error «ORA-01008: not all variables bound»
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := ‘CM_DEPT’;
v_col_string varchar2(60) := ‘DEPTNO,DNAME’;
v_values_string varchar2(60) := ‘:1,:2’;
v_using_string varchar2(256):= ’10,’||»’ACCOUNTING»’;
BEGIN
v_opr_sql := ‘INSERT INTO ‘||v_table_opr||’ ‘||
‘(‘||v_col_string||’)’||’ ‘||
‘VALUES’||’ ‘||'(‘||v_values_string||’)’;
EXECUTE IMMEDIATE v_opr_sql using v_using_string;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

Example 2: Works with literals
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := ‘CM_DEPT’;
v_col_string varchar2(60) := ‘DEPTNO,DNAME’;
v_values_string varchar2(60) := ‘:1,:2’;
BEGIN
v_opr_sql := ‘INSERT INTO ‘||v_table_opr||’ ‘||
‘(‘||v_col_string||’)’||’ ‘||
‘VALUES’||’ ‘||'(‘||v_values_string||’)’;
EXECUTE IMMEDIATE v_opr_sql using 10, ‘ACCOUNTING’;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

Example 3: Works with variables
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := ‘CM_DEPT’;
v_col_string varchar2(60) := ‘DEPTNO,DNAME’;
v_values_string varchar2(60) := ‘:1,:2’;
v_deptno number := 10;
v_dname varchar2(60) := ‘ACCOUNTING’;
BEGIN
v_opr_sql := ‘INSERT INTO ‘||v_table_opr||’ ‘||
‘(‘||v_col_string||’)’||’ ‘||
‘VALUES’||’ ‘||'(‘||v_values_string||’)’;
EXECUTE IMMEDIATE v_opr_sql using v_deptno, v_dname;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

Thanks
cmovva

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?

[{«Product»:{«code»:»SSFUEU»,»label»:»IBM OpenPages with Watson»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Version Independent»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]