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?

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.

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.

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