包和包体的代码如下:
包:
create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程,用游标变量作为返回参数
end TestPackage;包体:
create or replace package body TestPackage is
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from dhthxemployee;
end GetRecords;
end TestPackage;C#代码:
strCon = ConfigurationManager.ConnectionStrings["MyOracle"].ConnectionString;
con = new OracleConnection(strCon);
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "TestPackage.GetRecords";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ret_cursor",OracleType.Cursor);
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
con.Open();
OracleDataReader dr = cmd.ExecuteReader();
int i = 1;
while (dr.Read())
{
Console.WriteLine("Record:",i++);
Console.WriteLine("ID:{0}, Name:{1}, Position:{2}",dr.GetOracleNumber(0),dr.GetOracleString(1),dr.GetOracleString(2));
Console.WriteLine();
}
dr.Close();
con.Close();运行时报如下错误:
ORA-06500:第1行,第7列;
ORA-00306:调用'GetRecords'时参数个数或类型错误
ORA-06500:第1行,第7列;
PL/SQL:Statement ignored看参数应该没有问题呀,没看出来原因,请大家帮忙
包:
create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程,用游标变量作为返回参数
end TestPackage;包体:
create or replace package body TestPackage is
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from dhthxemployee;
end GetRecords;
end TestPackage;C#代码:
strCon = ConfigurationManager.ConnectionStrings["MyOracle"].ConnectionString;
con = new OracleConnection(strCon);
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "TestPackage.GetRecords";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ret_cursor",OracleType.Cursor);
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
con.Open();
OracleDataReader dr = cmd.ExecuteReader();
int i = 1;
while (dr.Read())
{
Console.WriteLine("Record:",i++);
Console.WriteLine("ID:{0}, Name:{1}, Position:{2}",dr.GetOracleNumber(0),dr.GetOracleString(1),dr.GetOracleString(2));
Console.WriteLine();
}
dr.Close();
con.Close();运行时报如下错误:
ORA-06500:第1行,第7列;
ORA-00306:调用'GetRecords'时参数个数或类型错误
ORA-06500:第1行,第7列;
PL/SQL:Statement ignored看参数应该没有问题呀,没看出来原因,请大家帮忙
p1.Direction=System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.AddWithValue 好像不是这样用的.
这句代码写错了,笔误呀
应该是:
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
cmd.CommandText = "TestPackage.GetRecords";
cmd.Parameters.Add("ret_cursor", OracleType.Cursor).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); OracleDataReader dr = (OracleDataReader)cmd.Parameters["ret_cursor"].Value;
while (dr.Read())
{
//dr[0]
}