我正在使用提供基于Oracle函数的数据库API的产品，并且我可以通过ODP.NET调用函数。但是，我无法弄清楚，如何调用一个包含Ref Cursor作为Out参数的函数。到目前为止，我发现的所有样本或者使用Out参数调用过程，或者使用Ref Cursor作为返回值调用函数。我试着类似地定义参数，但不断收到错误提供了错误的数字或参数类型。
这里是函数头（显然是模糊的）：
函数GetXYZ（
uniqueId IN somepackage.Number_Type，
resultItems OUT somepackage.Ref_Type）
返回somepackage.Error_Type;
以下是somepackage中的类型定义：
SUBTYPE Number_Type IS NUMBER（13）;
TYPE Ref_Type IS REF CURSOR;
SUBTYPE Error_Type IS NUMBER;
这是我试过的代码：
string sql =otherpackage.GetXYZ;
var getXYZCmd = OracleCommand oracleConnection.CreateCommand（sql）;
getXYZCmd.CommandType = CommandType.StoredProcedure;
getXYZCmd.Parameters.Add（uniqueId，OracleDbType.Int32）.Value = uniqueExplosionId;
getXYZCmd.Parameters.Add（resultItems，OracleDbType.RefCursor）.Direction = ParameterDirection.Output;
getXYZCmd.Parameters.Add（return_value，OracleDbType.Int32）.Direction = ParameterDirection.ReturnValue;
我尝试了以下不同的方式来调用函数（当然每次只有一个）：
var result = getXYZCmd.ExecuteNonQuery（）;
var reader = getXYZCmd.ExecuteReader（）;
var scalarResult = getXYZCmd.ExecuteScalar（）;
但它们每个都会失败并显示错误消息：
Oracle.DataAccess.Client.OracleException：ORA-06550：第1行第15列：
PLS-00306：调用'GETXYZ时参数的数量或类型错误'
ORA-06550：第1行第15列：
PLS-00306：调用'GETXYZ'时参数的数量或类型错误
ORA-06550：第1行第7列：
PL / SQL：语句被忽略。
因此，通常可以使用Ref Cursor作为Out参数从C＃调用带ODP的函数。净？我可以用一个Varchar2-Out参数代替Ref Cursor调用一个具有相同结构的函数，而不会出现问题...
顺便说一句，我使用的是ODP.NET版本2.112.2.0从Visual Studio 2008中的C＃.NET 3.5开始。
预先感谢您的帮助！
你当然可以。有几个小问题要警惕，但这里是一个测试用例
创建或替换函数testodpRefCursor（
uniqueId IN NUMBER
，resultItems OUT NOCOPY SYS_REFCURSOR）RETURN NUMBER
IS
$ b BEGIN
OPEN resultItems用于从级别双连接中选择级别<唯一身份 ;
返回1;
END testodpRefCursor;
- 我发现
函数喜欢使
ReturnValue为 第一 $ pa
BindByName默认为FALSE，所以它默认为BIND BY POSITION
否则它非常简单：
pre $ OracleCommand cmd = new OracleCommand（TESTODPREFCURSOR，con ）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
//绑定
OracleParameter oparam = cmd.Parameters.Add（ReturnValue，OracleDbType.Int64）;
oparam.Direction = ParameterDirection.ReturnValue;
OracleParameter oparam0 = cmd.Parameters.Add（uniqueId，OracleDbType.Int64）;
oparam0.Value = 5;
oparam0.Direction = ParameterDirection.Input;
OracleParameter oparam1 = cmd.Parameters.Add（resultItems，OracleDbType.RefCursor）;
oparam1.Direction = ParameterDirection.Output;
//执行命令
OracleDataReader reader;
尝试
{
reader = cmd.ExecuteReader（）; （reader.Read（））{
Console.WriteLine（level：{0}，reader.GetDecimal（0））;
while
}
} ...
现在查看更多示例转到您的Oracle Home目录，查看@ ODP.NET中的Ref cursor示例。
例如：
％oracle client home％\odp.net\\ \\ samples \ 4 \RefCursor
I'm using a product that provides a database API based on Oracle functions and I'm able to call functions via ODP.NET in general. However, I can't figure out, how to call a function that includes a Ref Cursor as Out-parameter. All the samples I found so far either call a procedure with Out-parameter or a function with the Ref Cursor as return value. I tried to define the parameters similiarly, but keep getting the error that the wrong number or type of parameters is supplied.
Here is the function header (obviously obfuscated):
FUNCTION GetXYZ(
uniqueId IN somepackage.Number_Type,
resultItems OUT somepackage.Ref_Type)
RETURN somepackage.Error_Type;
These are the type definitions in "somepackage":
SUBTYPE Number_Type IS NUMBER(13);
TYPE Ref_Type IS REF CURSOR;
SUBTYPE Error_Type IS NUMBER;
And this is the code that I have tried:
string sql = "otherpackage.GetXYZ";
var getXYZCmd = OracleCommand oracleConnection.CreateCommand(sql);
getXYZCmd.CommandType = CommandType.StoredProcedure;
getXYZCmd.Parameters.Add("uniqueId", OracleDbType.Int32).Value = uniqueExplosionId;
getXYZCmd.Parameters.Add("resultItems", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
getXYZCmd.Parameters.Add("return_value", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue;
The I tried the following different ways to call the function (of course only one at a time):
var result = getXYZCmd.ExecuteNonQuery();
var reader = getXYZCmd.ExecuteReader();
var scalarResult = getXYZCmd.ExecuteScalar();
But each of them fails with the error message:
Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'GETXYZ'
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'GETXYZ'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.
So is it generally possible to call a function with a Ref Cursor as Out-parameter from C# with ODP.NET? I can call a function with the same structure with a Varchar2-Out-parameter instead of the Ref Cursor without problems...
Btw, I'm using ODP.NET version 2.112.2.0 from C#.NET 3.5 in Visual Studio 2008.
Thanks in advance for your help!
You sure can. There are a few gotchas to be wary of but here is a test case
create or replace function testodpRefCursor(
uniqueId IN NUMBER
,resultItems OUT NOCOPY SYS_REFCURSOR) RETURN NUMBER
IS
BEGIN
OPEN resultItems for select level from dual connect by level < uniqueId ;
return 1;
END testodpRefCursor;
- I have found thatfunctions likes to have theReturnValue as THE FIRST paramin the collection
- BindByName is by default FALSE, so it defaults to BIND BY POSITION
Otherwise it is quite straight forward:
OracleCommand cmd = new OracleCommand("TESTODPREFCURSOR", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
// Bind
OracleParameter oparam = cmd.Parameters.Add("ReturnValue", OracleDbType.Int64);
oparam.Direction = ParameterDirection.ReturnValue ;
OracleParameter oparam0 = cmd.Parameters.Add("uniqueId", OracleDbType.Int64);
oparam0.Value = 5 ;
oparam0.Direction = ParameterDirection.Input;
OracleParameter oparam1 = cmd.Parameters.Add("resultItems", OracleDbType.RefCursor);
oparam1.Direction = ParameterDirection.Output;
// Execute command
OracleDataReader reader;
try
{
reader = cmd.ExecuteReader();
while(reader.Read() ){
Console.WriteLine("level: {0}", reader.GetDecimal(0));
}
} ...
Now for more samples go to your Oracle Home directory and look @ the Ref cursor samples in ODP.NET
for instance:%oracle client home%\odp.net\samples\4\RefCursor
hth
这篇关于如何使用Ref Cursor作为Out参数从C＃中调用Oracle函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！