ORA-01008 with all variables bound

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?

---

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.

---

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.

---

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

---

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.

---

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