将Oracle plsql存储过程调用转换为c#时出现问题(数组绑定大小无效)

Question

我需要使用odp.net12将对PL/SQL中使用的存储过程的调用转换为c#代码。

存储过程获取自定义类型的一些参数。

这是我的存储过程调用:

DECLARE
  P_SCODSERIE    VARCHAR2(3);
  P_SCODUSER     VARCHAR2(30);
  P_SMODACESS    VARCHAR2(8);
  P_SVETCODOBS   LINEUP.PCK_PIR_TYPES.VT_ARRAY_STRING;
  P_SVETDSCCOMPL LINEUP.PCK_PIR_TYPES.VT_ARRAY_FORMULA;
  P_NVETITEM     LINEUP.PCK_PIR_TYPES.VT_ARRAY_INT;
  R_NCODERROR    NUMBER;
BEGIN
  P_SCODSERIE := NULL;
  P_SCODUSER := 'TSR';
  P_SMODACESS := '00000001';
  P_SVETCODOBS(1) := '03';
  P_SVETDSCCOMPL(1) := 'comments about the item';
  P_NVETITEM(1) := 1;
  LINEUP.PCK_PIR_REQUEST_CMP.INSERTORDERCMP(
          P_SCODSERIE => P_SCODSERIE,
          P_SCODUSER => P_SCODUSER,
          P_SMODACESS => P_SMODACESS,
          P_SVETCODOBS => P_SVETCODOBS,
          P_SVETDSCCOMPL => P_SVETDSCCOMPL,
          P_NVETITEM => P_NVETITEM,
          R_NCODERROR => R_NCODERROR );
END;
/

当我们使用odp.net将此调用转换为c#代码时，我得到一个错误

System.InvalidOperationException: OracleParameter.ArrayBindSize

但是我找不到我的参数错误。

我的c#代码:

Oracle.DataAccess.Client.OracleCommand objCmd
    = new Oracle.DataAccess.Client.OracleCommand();
objCmd.Connection = (Oracle.DataAccess.Client.OracleConnection) conn;
objCmd.CommandText = "LINEUP.PCK_PIR_REQUEST_CMP.INSERTORDERCMP";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.BindByName = true;

objCmd.Parameters
      .Add("P_SCODSERIE", Oracle.DataAccess.Client.OracleDbType.Varchar2).Direction
      = ParameterDirection.Input;
objCmd.Parameters["P_SCODSERIE"].Value = null;

objCmd.Parameters
      .Add("P_SCODUSER ", Oracle.DataAccess.Client.OracleDbType.Varchar2).Direction
      = ParameterDirection.Input;
objCmd.Parameters["P_SCODUSER "].Value = "TSR";

objCmd.Parameters
      .Add("P_SMODACESS", Oracle.DataAccess.Client.OracleDbType.Varchar2).Direction
      = ParameterDirection.Input;
objCmd.Parameters["P_SMODACESS"].Value = "00000001";

Oracle.DataAccess.Client.OracleParameter strVetCodObs
    = new Oracle.DataAccess.Client.OracleParameter("P_SVETCODOBS", OracleDbType.Varchar2, ParameterDirection.Input);
strVetCodObs.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
strVetCodObs.Value = new String[1] { "03" };
strVetCodObs.Size = 1;
strVetCodObs.UdtTypeName = "LINEUP.PCK_PIR_TYPES.VT_ARRAY_STRING";
objCmd.Parameters.Add(strVetCodObs);

Oracle.DataAccess.Client.OracleParameter strVetDescCompl
    = new Oracle.DataAccess.Client.OracleParameter("P_SVETDSCCOMPL", OracleDbType.Varchar2, ParameterDirection.Input);
strVetDescCompl.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
strVetDescCompl.Value = new String[1] { "comments about the item" };
strVetDescCompl.Size = 1;
strVetDescCompl.UdtTypeName = "LINEUP.PCK_PIR_TYPES.VT_ARRAY_FORMULA";
objCmd.Parameters.Add(strVetDescCompl);

Oracle.DataAccess.Client.OracleParameter nVetItem
    = new Oracle.DataAccess.Client.OracleParameter("P_NVETITEM", OracleDbType.Decimal, ParameterDirection.Input);
nVetItem.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
nVetItem.Value = new Int32[1] { 1 };
nVetItem.Size = 1;
nVetItem.UdtTypeName = "LINEUP.PCK_PIR_TYPES.VT_ARRAY_INT";
objCmd.Parameters.Add(nVetItem);
objCmd.Parameters
      .Add("R_NCODERRO", Oracle.DataAccess.Client.OracleDbType.Decimal).Direction
      = ParameterDirection.Output;

if (conn.State != ConnectionState.Open) {
   conn.Open();
}
objCmd.ExecuteNonQuery();

Answer 1

你的问题(我认为)在这一行:

objCmd.CommandText = "LINEUP.PCK_PIR_REQUEST_CMP.INSERTORDERCMP";

在后面的代码行中，您将按名称将c#变量绑定到CommandText中定义的语句-您错过的部分是在其中定义绑定变量。您需要将这一行更改为:

objCmd.CommandText = "LINEUP.PCK_PIR_REQUEST_CMP.INSERTORDERCMP(
  P_SCODSERIE => :P_SCODSERIE,
  P_SCODUSER => :P_SCODUSER ,
  P_SMODACESS=> :P_SMODACESS,
  P_SVETCODOBS => :P_SVETCODOBS,
  P_SVETDSCCOMPL => :P_SVETDSCCOMPL,
  P_NVETITEM => :P_NVETITEM,
  R_NCODERROR => :R_NCODERROR 
)";

然后代码:

objCmd.Parameters["P_SCODSERIE"].Value = null;

将看到用'null'值代替语句':P_SCODSERIE'中的占位符