C#预言机试图传递字符串参数-ORA-01722:无效数字

本文关键字：-ORA-01722 参数 无效 数字 字符串 预言机 | 更新日期: 2023-09-27 18:25:54

我正在尝试使用oracle参数传递参数。它可以很好地处理int类型，但对于我的字符串类型，它失败了，错误是：

ORA-01722: invalid number
[OracleException (0x6ba): ORA-01722: invalid number]
   OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) +1047
   OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isDescribeOnly, Boolean isFromEF) +13029
   Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +2739
   Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader() +210
   Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteScalar() +406
   AspWebReqManagedOracleTest.ParameterizedSql.ExecScalarQuery() in c:'max'git_root'oboe-test'automation'chef-repo'site-cookbooks'fixture'files'default'win_dotnet_sample_apps'AspWebReqManagedOracleTest'ParameterizedSql.aspx.cs:68
   AspWebReqManagedOracleTest.ParameterizedSql.Page_Load(Object sender, EventArgs e) in c:'max'git_root'oboe-test'automation'chef-repo'site-cookbooks'fixture'files'default'win_dotnet_sample_apps'AspWebReqManagedOracleTest'ParameterizedSql.aspx.cs:21
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
   System.Web.UI.Control.OnLoad(EventArgs e) +92
   System.Web.UI.Control.LoadRecursive() +54
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

这是我的代码：

OracleConnection conn = new OracleConnection(connectionString);
try
{
    conn.Open();
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT count(*) from SALES_ADVENTUREWORKS2012.SALESORDERDETAIL where (PRODUCTID=:productid or ROWGUID like :rowguid) and ROWNUM<:limit";
    cmd.Parameters.Add(new OracleParameter("limit", 90));
    cmd.Parameters.Add(new OracleParameter("productid", 773));
    OracleParameter par = new OracleParameter("rowguid", OracleDbType.Char);
    par.Value = "hi";
    cmd.Parameters.Add(par);
    cmd.CommandType = CommandType.Text;
    object o = cmd.ExecuteScalar();
}
finally
{
    conn.Close();
}

请注意，如果我传递嵌入查询中的字符串参数rowguid，它可以正常工作（因此"limit"answers"productid"参数没有问题）。我尝试过使用Char、NChar、NVarchar2；还尝试指定大小。有什么想法吗？

C#预言机试图传递字符串参数-ORA-01722:无效数字

正如我所说，当不使用参数时，查询工作正常。为了让它发挥作用，我不得不删除最后一个条件：

where ROWNUM<:limit
相关文章：