c# - ORA-06502、ORA-06512 Oracle 存储过程错误

Question

这些是我尝试从我的 C# 代码执行存储过程时遇到的错误。

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MYAPP.PRO_COMPANYSEARCH", line 28
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

我的 C# 代码如下所示:

using (var mCon = new OracleConnection(MyConnectionString))
{
    myCon.Open();

    using (OracleCommand myOracleCmd = myCon.CreateCommand())
    {
        myOracleCmd.CommandType = CommandType.StoredProcedure;
        myOracleCmd.CommandText = "PRO_COMPANYSEARCH";
        myOracleCmd.Parameters.Add("o_result_cur", OracleDbType.RefCursor, ParameterDirection.Output);
        myOracleCmd.Parameters.Add("o_sqlcode", OracleDbType.Int32, ParameterDirection.Output);
        myOracleCmd.Parameters.Add("o_sqlmsg", OracleDbType.Varchar2, ParameterDirection.Output);

        var reader = myOracleCmd.ExecuteReader();
        dtCompany.Load(reader);
        reader.Dispose();
    }
}

输出参数有3个，2个用于跟踪异常，1个用于结果。

编辑:这是我的存储过程，(真不敢相信我忘记发布了)

CREATE OR REPLACE
PROCEDURE PRO_COMPANYSEARCH (
    o_result_cur OUT SYS_REFCURSOR,
    o_sqlcode OUT NUMBER,
    o_sqlmsg OUT VARCHAR2)

IS
BEGIN
  o_sqlmsg  := 'SUCCESS';
  o_sqlcode := 0;
  OPEN o_result_cur FOR Select distinct irint, irname, irabbv, iropt From vw_issue;

EXCEPTION
WHEN NO_DATA_FOUND THEN
  o_sqlcode := SQLCODE * -1;
  o_sqlmsg  := 'NO DATA FOUND';
WHEN OTHERS THEN
  o_sqlcode := SQLCODE * -1;     --This is line 28, I removed unnecessary comments.
  o_sqlmsg  := SUBSTR(SQLERRM, 1, 200);
END PRO_COMPANYSEARCH;

Answer 1

我找到了答案，在 C# 代码中你还必须包含 varchar 的大小

using (var mCon = new OracleConnection(MyConnectionString))
{
    myCon.Open();

    using (OracleCommand myOracleCmd = myCon.CreateCommand())
    {
        myOracleCmd.CommandType = CommandType.StoredProcedure;
        myOracleCmd.CommandText = "PRO_COMPANYSEARCH";
        myOracleCmd.Parameters.Add("o_result_cur", OracleDbType.RefCursor, ParameterDirection.Output);
        myOracleCmd.Parameters.Add("o_sqlcode", OracleDbType.Int32, ParameterDirection.Output);
        myOracleCmd.Parameters.Add("o_sqlmsg", OracleDbType.Varchar2, ParameterDirection.Output);
        myOracleCmd.Parameters["O_sqlmsg"].Size = 255;

        myOracleCmd.ExecuteNonQuery();
        var myReader = ((OracleRefCursor)myOracleCmd.Parameters["o_result_cur"].Value).GetDataReader();


        dtCompany.Load(myReader);
    }
}