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

Are you able to use DBMS_OUTPUT to print out the SQL statement before it is executed? — OMG Ponies, Sep 14 '09 at 15:47

score 50 · Accepted Answer · answered Sep 15 '09 at 07:38

50

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;
    ...
}

answered Sep 15 '09 at 07:38

Christian13467

5,324
31
34

that makes more sense... I re-arranged the parameters yesterday and realized it was binding by the position of the variable and not the name (which was no good); didn't realize there was an option to change that – John Sep 15 '09 at 12:02
This is good Christian13467! I didn't know that thus my experience with the native .Net Oracle Data Provider from 8i to 10g. Thanks for this answer. – Will Marcouiller Sep 15 '09 at 17:57
1

Excellent solution. Much better than duplicating the parameters. @John - you should really consider accepting this answer, if I may - over 13000 people have seen your question already, they should have seen the better option first. (Sorry Tony) – Kobi Sep 18 '11 at 09:25
1

This just resolved an hour of frustration and head scratching over type errors in an insert - thanks! So much for assuming that the names in my named parameters were, well, actually being USED. Sigh. – Mar 07 '12 at 07:13
This is horrible design by oracle. +1 – Jimenemex May 23 '18 at 14:07

score 26 · Answer 2 · answered Sep 14 '09 at 15:55

26

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> 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> 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.

answered Sep 14 '09 at 15:55

Tony Andrews

129,880
21
220
259

That worked... can't believe it would act in that manor, but it worked, so i'll take it! Thanks! – John Sep 14 '09 at 15:59
1

Not only do they have to be specified multiple times, but they have to be added in the order they appear in the query. – Fls'Zen Mar 18 '11 at 17:53
1

I don't mean to piggyback onto a very old post ... but does anyone know if this (having to add parameters multiple times if used multiple times) is still the case? I am dealing with a strange scenario where something works locally for me, but failed when it was ran by a user in Production. Backed out the change for now, but somewhat at a loss for why the `01008` error only showed up to the user. – Nick Jun 17 '15 at 21:21

score 2 · Answer 3 · answered Sep 19 '11 at 16:11

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, '%') || '%'

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

3 Answers3

Linked