使用ODP.net更新表时出现错误ORA-01722 [英] Error ORA-01722 when updating a table using ODP.net

问题描述

我正在尝试更新一个看起来像这样的表:

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

column a VARCHAR2(80)

使用以下功能:

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

但获得" ORA-01722无效号码"例外.

我尝试

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

和

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

和

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

推荐答案

默认情况下，ODP.Net按提供的顺序(而不是按名称)绑定参数，并且您要在第一个参数之前指定第二个参数codigo domicilio.按顺序绑定意味着该参数的名称将被忽略.

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.

更改命令绑定到名称(cmd.BindByName = true) ，或按照命令中使用的顺序提供参数.

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.

如果这是一个大项目，我建议创建一个工厂水暖方法以返回将设置为BindByName的OracleCommand s

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

这篇关于使用ODP.net更新表时出现错误ORA-01722的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！