C# 读取Oracle存储过程记录集错误解决办法[转]

在C#中使用游标返回Oracle的存储过程记录集时
如果程序中参数的名字和Oracle存储过程输出参数的名字不一样，则会出现一下错误

未处理的“System.Data.OracleClient.OracleException”类型的异常出现在 system.data.dll 中。

其他信息: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ALLTEST1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

解决办法如下：

    修改存储过程中输出参数的名称，使其和程序中生成的输入参数名称保持一致，
    也可以修改程序中输出参数的名称，使其和Oracle存储过程中的名称保持一致。

另外：关闭游标不用手动进行关闭，open mycursor for select * from table ;以后，Oracle会自动关闭游标。

以下为程序代码：
    1.数据包代码

create or replace package demo
is
  type curDemo is ref Cursor;
  procedure getDemo
  (userCurDemo out demo.curDemo);
end demo;

create or replace package body demo
as
  procedure getDemo
  (userCurDemo out demo.curDemo)
  is
  begin
    open userCurDemo for select ssn,fname,lname from person;
  end getDemo;
end demo;

    2.程序代码


OracleConnection oraCn = new OracleConnection("user id=blog;password=myblog;data source=db_192.168.0.106");
   OracleCommand oraCmd = new OracleCommand();   
   oraCmd.CommandText = "demo.getDemo";
   oraCmd.CommandType = CommandType.StoredProcedure;
   oraCmd.Parameters.Clear();
   oraCmd.Connection = oraCn;
   OracleParameter cursor = new OracleParameter("pGetTest11",OracleType.Cursor,260,ParameterDirection.Output,true,0,0,"",DataRowVersion.Default,Convert.DBNull);
   oraCmd.Parameters.Add(cursor);
   OracleDataAdapter oraDa = new OracleDataAdapter();
   oraDa.SelectCommand = oraCmd;
   oraCn.Open();
   DataSet ds = new DataSet();
   oraDa.Fill(ds,"Test1");
   dataGrid1.DataSource = ds.Tables["Test1"].DefaultView;
   oraCn.Close();

此处会报错

修改为以下代码后，错误消失

OracleConnection oraCn = new OracleConnection("user id=blog;password=myblog;data source=db_192.168.0.106");
   OracleCommand oraCmd = new OracleCommand();
   oraCmd.CommandText = "demo.getDemo";
   oraCmd.CommandType = CommandType.StoredProcedure;
   oraCmd.Parameters.Clear();
   oraCmd.Connection = oraCn;
   OracleParameter cursor = new OracleParameter("userCurDemo",OracleType.Cursor,260,ParameterDirection.Output,true,0,0,"",DataRowVersion.Default,Convert.DBNull);
   oraCmd.Parameters.Add(cursor);
   OracleDataAdapter oraDa = new OracleDataAdapter();
   oraDa.SelectCommand = oraCmd;
   oraCn.Open();
   DataSet ds = new DataSet();
   oraDa.Fill(ds,"Test1");
   dataGrid1.DataSource = ds.Tables["Test1"].DefaultView;
   oraCn.Close();