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个用于跟踪异常，另一个用于结果。

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

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

Answer 2

在我的例子中，上面的答案几乎没有什么改正。

var output_result_string = new OracleParameter("OUTPUT_RESULT_STRING", 
OracleDbType.NVarchar2, xxx, ParameterDirection.Output);
output_result_string.Size = 2500;

Answer 3

using (OracleConnection connection = new OracleDbConnector().OracleConnection)

using (OracleCommand cmd = connection.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "MyFunction";
    cmd.Parameters.Add("returnVal", OracleDbType.Varchar2, ParameterDirection.ReturnValue);
    cmd.Parameters["returnVal"].Size = 200;
    cmd.Parameters.Add("application", OracleDbType.Varchar2, "InputValue", ParameterDirection.Input);
    cmd.ExecuteNonQuery();
    string returnValue = cmd.Parameters["returnVal"].Value.ToString();
}