Phil's Notes

ORA-01722 invalid number in Parameterized Query

c#oracle

If you are receiving the ORA-01722 (invalid number) error in an Oracle query even though you are using named parameters, you may want to check to make sure that BindByName has been set to true.

For example, the code below will fail because the parameters aren’t truly bound by name so the parameters should be added in the same order that they appear in the query unless you use the BindByName option.

string oradb = "your connection string";
 
var connection = new OracleConnection(oradb);
 
connection.Open();
 
string query = @"select *
                  from table
                  where numeric_field = :numeric_field
                  and character_field :character_field";
 
var command = new OracleCommand(query, connection);
command.CommandType = CommandType.Text;
 
command.Parameters.Add(new OracleParameter("character_field", "some value"));
command.Parameters.Add(new OracleParameter("numeric_field", 123));
 
/* If you uncomment the line below, the code will work even though the parameters aren't added in the same order
  as they appear in the query because they will be truly bound by name. */
// command.BindByName = true;
 
var dataAdapter = new OracleDataAdapter(command);
 
var dataTable = new DataTable();
 
try
{
  dataAdapter.Fill(dataTable);
}
catch (OracleException ex)
{
  Console.WriteLine($"Exception: {ex.Message}");
}
 
connection.Close();
connection.Dispose();