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?

Hyperacidity

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

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? – Krissykrista

@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.) – Hyperacidity

I meant ODP.NET. 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). – Hyperacidity

Answer 1

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.

Hyperacidity

Answer 2

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.

Stonwin

Answer 3

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));

Jhelum

Answer 4

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.

Olivero

Yeah, I would rather use what both oracle and microsoft recommends, but had to put my attempts on hold. With five-digit error-codes I'm a bit disappointed about the generic messages. ;) – Hyperacidity

Answer 5

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());

Ivelisseivens