在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();