ORA-01008 with all variables bound

Question

I'm using System.Data.OracleClient which does parameter-binding by name and are verifying that CommandText and Parameters are in sync:

    public string CommandText { get; set; }
    public IEnumerable<OracleParameter> Parameters { get; set; }

    private void VerifyThatAllParametersAreBound()
    {
        var variableNames = Regex.Matches(CommandText, ":\\w+")
            .Cast<Match>().Select(m => m.Value).ToArray();
        var parameteterNames = Parameters.Select(p => p.ParameterName).ToArray();

        var unboundVariables = variableNames.Except(parameteterNames).ToArray();
        if (unboundVariables.Length > 0)
        {
            throw new Exception("Variable in CommandText missing parameter: "
                + string.Join(", ", unboundVariables) + ".");
        }

        var unboundParameters = parameteterNames.Except(variableNames).ToArray();
        if (unboundParameters.Length > 0)
        {
            throw new Exception("Parameter that is not used in CommandText: "
                + string.Join(", ", unboundParameters) + ".");
        }
    }

Still one query throws ORA-01008: not all variables bound. When manually inserting the parameter-values into the offending CommandText the query runs, so the CommandText and Parameters-values should be ok. I'm using : as prefix both for variables and parameternames and it's working for the other queries.

How can I pinpoint the cause of this exception?

do you have the code making the queries? Also, have you tried with the oracle (or other vendors) .net clients? I think Microsoft are no longer providing the oracle client. — Nick Randell, Apr 15 '11 at 15:23
I vaguely remember an issue with one of the Oracle clients that the parameters had to be bound in exactly the same order they appeared in the query or it wouldn't work - could that be the problem here? — Rup, Apr 15 '11 at 16:09
@Nick - The command was an update with ~40 parameters so for readability and ip-reasons I dropped them. I tried with ODT.NET from Oracle but had trouble deploying it. (See also comment below.) — Grastveit, Apr 15 '11 at 17:49
I meant [ODP.NET](http://www.oracle.com/technetwork/topics/dotnet/index-085163.html). I will try with it again now that there might be a practical reason to use it. Since the error-code is from Oracle I think all parameters are transferred, but maybe it has something to do with datatypes? I create parameters like this `new OracleParameter(":myParam", obj.MyParam)`. — Grastveit, Apr 15 '11 at 18:49

score 9 · Accepted Answer · answered Apr 18 '11 at 10:12

The mistake was not specifing DBNull.Value for null-values. So

new OracleParameter(":Foo", item.Foo)

had to preplaced with

item.Foo == null 
    ? new OracleParameter(":Foo", DBNull.Value) 
    : new OracleParameter(":Foo", item.Foo)

I think it was working earlier with ODT.NET without null-checks, but have not confirmed it. Apparently System.Data.OracleClient is dropping parameters with null-value.

score 3 · Answer 2 · answered Dec 06 '12 at 09:38

3

If you pass null as parameter value, you get "Not all variables bound" If you pass DBNull.Value you get runtime error somewhere in the OracleClient. To pass NULL, use string.Empty, OracleClient converts it to NULL for any database type.

answered Dec 06 '12 at 09:38

Yaro

723
7
10

score 2 · Answer 3 · answered Aug 27 '19 at 15:13

If you have more than one parameter, you need to set BindByName to true. For example:

OracleCommand cmd = con.CreateCommand();

cmd.BindByName = true;

cmd.Parameters.Add(new OracleParameter("parameter1", parameter1));
cmd.Parameters.Add(new OracleParameter("parameter2", parameter2));

score 0 · Answer 4 · answered Apr 15 '11 at 16:03

0

I believe Microsoft has deprecated OracleClient as part of ADO.NET about 2 years ago.

You may want to consider using Oracle's data access components (ODAC odp.net). Easy to build-up (and check counts) of parameters using OracleParameter class. Setup and install docs found here. Oh, you can get into their Entity framework (and LINQ) support also (still beta i think?).

Something to seriously consider anyway.

answered Apr 15 '11 at 16:03

tbone

15,107
3
33
40

Yeah, I would rather use what both oracle and microsoft recommends, but had to put my attempts [on hold](http://stackoverflow.com/questions/5581752/program-defensively-against-odac-instantclient). With five-digit error-codes I'm a bit disappointed about the generic messages. ;) – Grastveit Apr 15 '11 at 17:47

score 0 · Answer 5 · answered Feb 07 '20 at 19:42

Based on above answers and comments, I made sure the following to resolve this issue:

parameters are being bound in the same order they appear in the query
parameter type is specified
If same parameter value is required more than once in the SQL, name each parameter differently in the SQL (not sure if this is required)

OracleParameter[] orclParams = new OracleParameter[] { new OracleParameter{ ParameterName = "param1", OracleDbType = OracleDbType.Varchar2, Value = "abc" }, new OracleParameter{ ParameterName = "param2", OracleDbType = OracleDbType.Varchar2, Value = "abc" }, new OracleParameter{ ParameterName = "date1", OracleDbType = OracleDbType.Date, Value = myDate } }; SomeFunction(sqlQuery, orclParams.ToList());

ORA-01008 with all variables bound

5 Answers5