ORA-01008 with all variables related

Question

I use System.Data.OracleClient , which binds parameters by name and checks if CommandText and Parameters are synchronized:

  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) + "."); } } 

Another request calls ORA-01008: not all variables bound . When parameter values ​​are manually inserted into a commandText-violating query, the query is executed, so the CommandText and Parameters values ​​should be in order. I use: as a prefix for both variables and parameternames, and it works for other queries.

How can I determine the cause of this exception?

Answer 1

The error did not indicate DBNull.Value for null values. So

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

should have been installed using

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

I think it worked before with ODT.NET without null checks, but did not confirm this. Apparently System.Data.OracleClient is a reset with zero value.

Answer 2

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

Answer 3

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

You might want to use Oracle Data Access Components (ODAC odp.net). It is easy to configure (and check the number) of parameters using the OracleParameter class. Install and install the documents found here . Oh, you can get into your Entity (and LINQ) infrastructure as well (beta still, I think?).

Something to think seriously.

Answer 4

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

 OracleCommand cmd = con.CreateCommand(); cmd.BindByName = true; cmd.Parameters.Add(new OracleParameter("parameter1", parameter1)); cmd.Parameters.Add(new OracleParameter("parameter2", parameter2));