Error ORA-01722 when updating a table using ODP.ne

问题:

I'm trying update a table that looks something like:

column a VARCHAR2(80)

Using the following function:

sqlString = "UPDATE TABLE SET domicilio = :p_domicilio WHERE codigo = :p_codigo";
string sqlCommandtext = sqlString; 
using (var cn = new OracleConnection("DATA SOURCE=XXX..."))
{
    cn.Open();

    using (OracleCommand commandInt32 = cn.CreateCommand())
    {
        cmd.CommandText = sqlCommandtext;
        cmd.Parameters.Add("p_codigo", OracleDbType.Int32, 34620, ParameterDirection.Input);

        cmd.Parameters.Add("p_domicilio", OracleDbType.Varchar2, ParameterDirection.Input).Value = domicilio;
        //cmd.Parameters.Add("p_domicilio", OracleDbType.Varchar2, domicilio, ParameterDirection.Input);

        cmd.ExecuteNonQuery();
    }
}

but get "ORA-01722 invalid number" exception.

I try

cmd.Parameters.Add("p_domicilio", OracleDbType.Varchar2, ParameterDirection.Input).Value = domicilio;

and

cmd.Parameters.Add("p_domicilio", OracleDbType.Varchar2, domicilio, ParameterDirection.Input);

and

    var pDomicilio = new Oracle.DataAccess.Client.OracleParameter()
    {
        DbType = DbType.String,
        Value = domicilio,
        Direction = ParameterDirection.Input,
        OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2,
        ParameterName = "p_domicilio",
    };

回答1:

By default, ODP.Net binds parameters by the Order they are provided, not by name, and you are specifying the second parameter codigo before the first parameter domicilio. Bind by order means the name of the parameter is ignored.

Either change the command Binding to Name (cmd.BindByName = true), or provide the parameters in the same order that they are used in your command.

If this is a big project, I would suggest creating a factory plumbing method for returning OracleCommands which will be set to BindByName