Oracle ORA-01008 not all variables bound Error w Parameters

Question

This is the first time I ve dealt with Oracle  and I m having a hard time understanding why I m receiving this error   I m using Oracle s ODT NET w  C  with the following code in a query s where clause   WHERE table Variable1    VarA   AND   VarB IS NULL OR table Variable2 LIKE         VarB           AND   VarC IS NULL OR table Variable3 LIKE  VarC           and I m adding the parameter values like so   cmd Parameters Add  VarA    24    cmd Parameters Add  VarB    test    cmd Parameters Add  VarC    1234      When I run this query  the server returns   ORA-01008  not all variables bound    If I comment out either of the  AND        lines  the query completes successfully     Why would the query run through alright if I m only querying with two parameters  but not with three   The error I m receiving doesn t even make sense

User · Answer

You might also consider removing the need for duplicated parameter names in your Sql by changing your Sql to

table.Variable2 LIKE '%' || :VarB || '%'

and then getting your client to provide '%' for any value of VarB instead of null. In some ways I think this is more natural.

You could also change the Sql to

table.Variable2 LIKE '%' || IfNull(:VarB, '%') || '%'

User · Answer

It seems daft  but I think when you use the same bind variable twice you have to set it twice   cmd Parameters Add  VarA    24    cmd Parameters Add  VarB    test    cmd Parameters Add  VarB    test    cmd Parameters Add  VarC    1234    cmd Parameters Add  VarC    1234      Certainly that s true with Native Dynamic SQL in PL SQL   SQL gt  begin   2     execute immediate  select   from emp where ename  name and ename  name    3     using  KING     4  end    5    begin   ERROR at line 1  ORA-01008  not all variables bound   SQL gt  begin   2     execute immediate  select   from emp where ename  name and ename  name     3     using  KING    KING     4  end    5     PL SQL procedure successfully completed

User · Answer

The ODP Net provider from oracle uses bind by position as default  To change the behavior to bind by name  Set property BindByName to true  Than you can dismiss the double definition of parameters   using OracleCommand cmd   con CreateCommand                  cmd BindByName   true

[sql] Oracle "ORA-01008: not all variables bound" Error w/ Parameters

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g