时间戳

首页 > 解决方案 > 从 C# 执行 Oracle 存储过程 - 调用中的参数数量或类型错误

c# - 从 C# 执行 Oracle 存储过程 - 调用中的参数数量或类型错误

问题描述

这是我描述存储过程时的输出：

 desc procedure_name  

 VCOMPTE    VARCHAR2                 IN            
 VRESULT    REF CURSOR               OUT           
 CLIENT_NO  VARCHAR2(6)              OUT           
 ACCT_NAME  VARCHAR2(35)             OUT           
 NCG        VARCHAR2(6)              OUT           
 NCG_DESC   VARCHAR2(35)             OUT           
 AGENCE     VARCHAR2(5)              OUT           
 TEL        VARCHAR2(50)             OUT

这是我用来执行它的 C# 代码：

public void Validate(string account_num)
{
    OracleConnection conn = new OracleConnection(HelperClass.GetConstring());         

    OracleCommand _cmdObj = conn.CreateCommand();
    _cmdObj.CommandText = "pk_xxx.procedure_name";
    _cmdObj.CommandTimeout = 1680;
    _cmdObj.CommandType = System.Data.CommandType.StoredProcedure;

    OracleParameter para_account_num = new OracleParameter();
    para_account_num.ParameterName = "VCOMPTE"; 
    para_account_num.OracleDbType = OracleDbType.Varchar2;
    para_account_num.Direction = System.Data.ParameterDirection.Input; 
    para_account_num.Value = account_num; 
    _cmdObj.Parameters.Add(para_account_num);

    OracleParameter VRESULT = new OracleParameter();
    VRESULT.ParameterName = "VRESULT";
    VRESULT.OracleDbType = OracleDbType.RefCursor;
    VRESULT.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(VRESULT);

    OracleParameter client_no = new OracleParameter();
    client_no.ParameterName = "CLIENT_NO"; 
    client_no.OracleDbType = OracleDbType.Varchar2;
    client_no.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(client_no);

    OracleParameter acct_name = new OracleParameter();
    acct_name.ParameterName = "ACCT_NAME";
    acct_name.OracleDbType = OracleDbType.Varchar2;
    acct_name.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(acct_name);

    OracleParameter ncg = new OracleParameter();
    ncg.ParameterName = "NCG";
    ncg.OracleDbType = OracleDbType.Varchar2;
    ncg.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(ncg);

    OracleParameter tr_desc = new OracleParameter();
    tr_desc.ParameterName = "NCG_DESC";
    tr_desc.OracleDbType = OracleDbType.Varchar2;
    tr_desc.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(tr_desc);

    OracleParameter AGENCE = new OracleParameter();
    AGENCE.ParameterName = "AGENCE";
    AGENCE.OracleDbType = OracleDbType.Varchar2;
    AGENCE.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(AGENCE);

    OracleParameter TEL = new OracleParameter();
    TEL.ParameterName = "TEL";
    TEL.OracleDbType = OracleDbType.Varchar2;
    TEL.Direction = System.Data.ParameterDirection.ReturnValue;
    _cmdObj.Parameters.Add(TEL);

    try
    {
        conn.Open();

        OracleDataReader reader = _cmdObj.ExecuteReader();

        while (reader.Read())
        {
            //I will use the data 
        }
    }
    catch (Exception xc)
    {
        //catch xc 
    }
}

但我得到一个错误

调用“procedure_name”时参数的数量或类型错误。”

调试对我来说很困难，因为我无法直接访问 Oracle 数据库，而且我不确定自己做错了什么。我已经在同一个数据库中成功执行了其他存储过程。提前致谢！

标签： c#oraclestored-proceduresparametersoracle10g

解决方案

ExecuteReader()仅用于函数，不用于过程。你必须使用ExecuteNonQuery()

在ExecuteNonQuery()你必须阅读结果之后，例如

_cmdObj.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)VRESULT.Value).GetDataReader();

或创建一个类似的功能

FUNCTION function_name RETURNS SYS_REFCURSOR

 VCOMPTE    VARCHAR2                 IN            
 CLIENT_NO  VARCHAR2(6)              OUT           
 ACCT_NAME  VARCHAR2(35)             OUT           
 NCG        VARCHAR2(6)              OUT           
 NCG_DESC   VARCHAR2(35)             OUT           
 AGENCE     VARCHAR2(5)              OUT           
 TEL        VARCHAR2(50)             OUT

像这样称呼它

OracleCommand _cmdObj = conn.CreateCommand();
_cmdObj.CommandText = "pk_xxx.procedure_name";
_cmdObj.CommandType = CommandType.StoredProcedure;

_cmdObj.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
_cmdObj.Parameters.Add("VCOMPTE", OracleDbType.Varchar2, ParameterDirection.Input).Value = account_num;
_cmdObj.Parameters.Add("CLIENT_NO", OracleDbType.Varchar2, ParameterDirection.Output);
_cmdObj.Parameters.Add("ACCT_NAME", OracleDbType.Varchar2, ParameterDirection.Output);
...
OracleDataReader reader = _cmdObj.ExecuteReader();

也许看看Data Provider for .NET Developer's Guide

推荐阅读