Oracle .NET 错误 - 参数数量或类型错误

Question

现在我知道以前已经有人问过这个问题了。 但我已经双重确保所有参数都是正确的并且没有拼写错误。 我仍然收到错误。 有人可以在这里指导我吗？ 我已经快无计可施了！

CREATE OR REPLACE PROCEDURE VWT.WUA_DELETE_FP_REQUEST 
    (i_pLDAPUserName IN varchar2,
      i_pIReasonCode IN number,
      i_pLastName IN varchar2,
      i_pFirstName IN varchar2,
      i_pDealerID IN number,
      i_pAddr1 IN varchar2,
      i_pAddr2 IN varchar2,
      i_pCity IN varchar2,
      i_pState IN varchar2, 
      i_pZip IN varchar2,
      i_pBusinessEmail IN varchar2,
      i_pBusinessPhone IN varchar2,
      i_pReviewDate IN Date,
      i_pReviewedByWhom IN varchar2,
      i_pDealersRepID IN varchar2,
      i_pComments IN varchar2,
      i_pPrivacyUsageFlag IN varchar2,
      i_pDealersBranchID IN varchar2,
      i_pPrivacyUsageDate IN date,
      i_pMarketingFlag IN varchar2,
      i_pMarketingDate IN date,
      i_pWorkCountry IN varchar2,
      i_pOperator IN varchar2,
      o_return_status OUT integer,
      o_error_desc OUT varchar2
)

=================================================== ======================

运行该过程的代码

 OracleParameter ldapUserName = new OracleParameter
         ("i_pLDAPUserName", OracleType.VarChar, 1024);
     ldapUserName.Direction = ParameterDirection.Input;
     ldapUserName.Value = ldapUserName_;
     command.Parameters.Add(ldapUserName);

 OracleParameter reasonCode = new OracleParameter
         ("i_pIReasonCode", OracleType.Number);
     reasonCode.Direction = ParameterDirection.Input;
     reasonCode.Value = reasonCode_;
     command.Parameters.Add(reasonCode);

 OracleParameter lastName = new OracleParameter
         ("i_pLastName", OracleType.VarChar, 1024);
     lastName.Direction = ParameterDirection.Input;
     lastName.Value = userDetail.LastName ?? String.Empty;
     command.Parameters.Add(lastName);

 OracleParameter firstName = new OracleParameter
         ("i_pFirstName", OracleType.VarChar, 1024);
     firstName.Direction = ParameterDirection.Input;
     firstName.Value = userDetail.FirstName ?? String.Empty;
     command.Parameters.Add(firstName);

 OracleParameter dealerID = new OracleParameter
          ("i_pDealerID", OracleType.Number);
     dealerID.Direction = ParameterDirection.Input;
     dealerID.Value = 
          Int32.Parse(String.IsNullOrEmpty(userDetail.DealerID) ? "0" : userDetail.DealerID);
     command.Parameters.Add(dealerID);

 OracleParameter address1 = new OracleParameter
          ("i_pAddr1", OracleType.VarChar, 1024);
     address1.Direction = ParameterDirection.Input;
     address1.Value = userDetail.WorkAddress1 ?? String.Empty;
     command.Parameters.Add(address1);

 OracleParameter address2 = new OracleParameter
          ("i_pAddr2", OracleType.VarChar, 1024);
      address2.Direction = ParameterDirection.Input;
      address1.Value = userDetail.WorkAddress2 ?? String.Empty;
      command.Parameters.Add(address2);

 OracleParameter city = new OracleParameter
           ("i_pCity", OracleType.VarChar, 1024);
     city.Direction = ParameterDirection.Input;
     city.Value = userDetail.City ?? String.Empty;
     command.Parameters.Add(city);

 OracleParameter state = new OracleParameter
           ("i_pState", OracleType.VarChar, 1024);
     state.Direction = ParameterDirection.Input;
     state.Value = userDetail.State ?? String.Empty;
     command.Parameters.Add(state);

 OracleParameter zip = new OracleParameter
           ("i_pZip", OracleType.VarChar, 1024);
     zip.Direction = ParameterDirection.Input;
     zip.Value = userDetail.Zip ?? String.Empty;
     command.Parameters.Add(zip);

 OracleParameter email = new OracleParameter
          ("i_pBusinessEmail", OracleType.VarChar, 1024);
     email.Direction = ParameterDirection.Input;
     email.Value = userDetail.EMail ?? String.Empty;
     command.Parameters.Add(email);

 OracleParameter phone = new OracleParameter
           ("i_pBusinessPhone", OracleType.VarChar, 1024);
      phone.Direction = ParameterDirection.Input;
      phone.Value = userDetail.Phone ?? String.Empty;
      command.Parameters.Add(phone);

 OracleParameter reviewDate = new OracleParameter
           ("i_pReviewDate", OracleType.DateTime);
     reviewDate.Direction = ParameterDirection.Input;
     reviewDate.Value = userDetail.ReviewedDate ?? (object)DBNull.Value;
     command.Parameters.Add(reviewDate);

 OracleParameter reviewedbyWhom = new OracleParameter
           ("i_pReviewedByWhom", OracleType.VarChar, 1024);
     reviewedbyWhom.Direction = ParameterDirection.Input;
     reviewedbyWhom.Value = userDetail.ReviewedByWhom ?? String.Empty;
     command.Parameters.Add(reviewedbyWhom);

 OracleParameter repID = new OracleParameter
           ("i_pDealersRepID", OracleType.VarChar, 1024);
     repID.Direction = ParameterDirection.Input;
     repID.Value = userDetail.RepID ?? String.Empty;
     command.Parameters.Add(repID);

 OracleParameter comments = new OracleParameter
          ("i_pComments", OracleType.VarChar, 1024);
     comments.Direction = ParameterDirection.Input;
     comments.Value = userDetail.Comments ?? String.Empty + comments_;
     command.Parameters.Add(comments);

 OracleParameter privacyUsageFlag = new OracleParameter
          ("i_pPrivacyUsageFlag", OracleType.VarChar, 1024);
     privacyUsageFlag.Direction = ParameterDirection.Input;
     privacyUsageFlag.Value = userDetail.PrivacyAndUsageReadFlag ?? String.Empty;
     command.Parameters.Add(privacyUsageFlag);

 OracleParameter dealersBranchID = new OracleParameter
          ("i_pDealersBranchID", OracleType.VarChar, 1024);
     dealersBranchID.Direction = ParameterDirection.Input;
     dealersBranchID.Value = userDetail.DealerBranchID ?? String.Empty;
     command.Parameters.Add(dealersBranchID);

 OracleParameter privacyUsageDate = new OracleParameter
           ("i_pPrivacyUsageDate", OracleType.DateTime);
     privacyUsageDate.Direction = ParameterDirection.Input;
     privacyUsageDate.Value = 
            userDetail.PrivacyAndUsageReadDate ??(object)DBNull.Value;
     command.Parameters.Add(privacyUsageDate);

 OracleParameter marketingFlag = new OracleParameter
           ("i_pMarketingFlag", OracleType.VarChar, 1024);
     marketingFlag.Direction = ParameterDirection.Input;
     marketingFlag.Value = userDetail.SendMarketingEmailFlag ?? String.Empty;
     command.Parameters.Add(marketingFlag);

 OracleParameter marketingDate = new OracleParameter
           ("i_pMarketingDate", OracleType.DateTime);
     marketingDate.Direction = ParameterDirection.Input;
     marketingDate.Value = 
           userDetail.SendMarketingEmailDate ?? (object)DBNull.Value;
     command.Parameters.Add(marketingDate);

 OracleParameter country = new OracleParameter
           ("i_pWorkCountry", OracleType.VarChar, 1024);
     country.Direction = ParameterDirection.Input;
     country.Value = userDetail.Country ?? String.Empty;
     command.Parameters.Add(country);

 OracleParameter oper = new OracleParameter
           ("i_pOperator", OracleType.VarChar, 1024);
     oper.Direction = ParameterDirection.Input;
     oper.Value = operator_;
     command.Parameters.Add(oper);

OracleParameter returnStatus = new OracleParameter
          ("o_return_status", OracleType.Int16);
     returnStatus.Direction = ParameterDirection.Output;
     command.Parameters.Add(returnStatus);

OracleParameter errorDesc = new OracleParameter
         ("o_error_desc", OracleType.VarChar, 1024);
     errorDesc.Direction = ParameterDirection.Output;
     command.Parameters.Add(errorDesc);

Answer 1

如果没有发生具体的错误，就很难给出具体的答案。 您发布的代码可能是正确的。 我认为在所有情况下都没有什么问题。 然而，这里有一些我会开始寻找的地方。

您的命令语句格式正确吗？ 所有 VarChar 参数都接受 1024 个字符吗？ 进入 VarChar 列的任何字符串是否超过 1024？ Int16的returnStatus类型是否正确？

无论如何，这些都是一些可以开始的地方，如果您发布有关该问题的一些更详细的信息，您可能会得到更多回复。

Answer 2

我看了你的代码，没有发现任何可疑的地方。

在类似的情况下，我会做一个非常愚蠢的测试：在过程声明和运行它的代码中注释掉除第一个参数之外的所有参数，然后测试：如果有错误，您就会知道它与第一个错误有关范围; 如果没有显示错误，则取消过程和代码中第二个参数的注释。

我知道这很愚蠢、耗时且烦人，但如果您真的觉得自己被困在角落里，这可以给您更准确的提示，说明问题出在哪里。

Answer 3

DealerID 被声明为数字，但作为字符串传递。

我责怪滚动条，没有看到 Int32.Parse()

Answer 4

没有使用 .Net 的经验，但我想知道 OracleType.Int16 是否是 INTEGER 参数的正确数据类型（根据 http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracletype(VS.71).aspx，作为数字，需要 OracleType.Number）？

OracleType.Int16：“这不是本机 Oracle 数据类型，但提供它是为了提高绑定输入参数时的性能。”