如何从 C# 调用带有 Ref Cursor 作为 Out 参数的 Oracle 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6360244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
提问by Martin Klinke
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.
我正在使用提供基于 Oracle 函数的数据库 API 的产品，并且通常我能够通过 ODP.NET 调用函数。但是，我不知道如何调用包含 Ref Cursor 作为 Out 参数的函数。到目前为止，我发现的所有样本要么调用带有 Out 参数的过程，要么调用带有 Ref Cursor 作为返回值的函数。我试图以类似的方式定义参数，但不断收到错误提供的参数数量或类型错误。
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":
这些是“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...
那么通常是否可以使用 ODP.NET 从 C# 调用带有 Ref Cursor 作为 Out 参数的函数？我可以使用 Varchar2-Out 参数而不是 Ref Cursor 调用具有相同结构的函数而不会出现问题......
Btw, I'm using ODP.NET version 2.112.2.0 from C#.NET 3.5 in Visual Studio 2008.
顺便说一句，我在 Visual Studio 2008 中使用来自 C#.NET 3.5 的 ODP.NET 版本 2.112.2.0。
Thanks in advance for your help!
在此先感谢您的帮助！
回答by Harrison
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 that functions likes to have the ReturnValue as THE FIRSTparam in the collection
- BindByName is by default FALSE, so it defaults to BIND BY POSITION
- 我发现函数喜欢将 ReturnValue 作为集合中的第一个参数
- BindByName 默认为 FALSE，所以默认为 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
现在要获取更多示例，请转到您的 Oracle 主目录并查看 @ODP.NET 中的 Ref 游标示例
for instance: %oracle client home%\odp.net\samples\4\RefCursor
例如：%oracle client home%\odp.net\samples\4\RefCursor
hth
第
