在 C# 中执行一个返回引用游标的 oracle 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3535405/
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
Execute an oracle Function that returns a reference cursor in C#
提问by Conrad Frix
I have an oracle package with a procedure that has a in out reference cursor. My understanding is that this is pretty standard.
我有一个 oracle 包，其中包含一个具有输入输出引用游标的过程。我的理解是，这是非常标准的。
What I didn't like is the fact that I had to write a ton of code to just see the output. So I asked this questionand it turns out I can get what I want by creating a function that wraps the procedure.
我不喜欢的是我必须编写大量代码才能看到输出。所以我问了这个问题，结果我可以通过创建一个包装过程的函数来得到我想要的。
Update:Looks like I don't need the function anymore but it may be worth knowing anyway for those curious see the original question and answer updates.
更新：看起来我不再需要该功能了，但对于那些好奇的人来说，无论如何它可能值得了解，请参阅原始问题和答案更新。
Here's the function
这是功能
FUNCTION GetQuestionsForPrint (user in varchar2)
RETURN MYPACKAGE.refcur_question
AS
OUTPUT MYPACKAGE.refcur_question;
BEGIN
MYPACKAGE.GETQUESTIONS(p_OUTPUT => OUTPUT,
p_USER=> USER ) ;
RETURN OUTPUT;
END;
and here's what I do to execute it in SQL Developer
这是我在 SQL Developer 中执行它的方法
var r refcursor;
exec :r := mypackage.getquestionsForPrint('OMG Ponies');
print r;
So from now on I'm probably going to add the ForPrint functions to all my procedures.
所以从现在开始，我可能会将 ForPrint 函数添加到我的所有程序中。
This got me thinking, maybe functions are what I want and I don't need procedures.
这让我想到，也许函数就是我想要的，而我不需要过程。
To test this I tried executing the function from .NET, except I can't do it. Is this really the way it is.
为了测试这一点，我尝试从 .NET 执行该函数，但我不能这样做。真的是这样吗。
using (OracleConnection cnn = new OracleConnection("Data Source=Test;User Id=Test;Password=Test;"))
{
cnn.Open();
OracleCommand cmd = new OracleCommand("mypackage.getquestionsForPrint");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add ( "p_USER", "OMG Ponies");
cmd.Connection = cnn;
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.GetOracleValue(0));
}
Console.ReadLine();
}
So I get the error.
所以我得到了错误。
getquestionsForPrint is not a procedure or is undefined
getquestionsForPrint is not a procedure or is undefined
I tried ExecuteScalar as well with the same result.
我也尝试了 ExecuteScalar，结果相同。
EDITTaking Slider345's advice I've also tried setting the command type to text and using the following statement and I get invalid SQL statement
编辑接受 Slider345 的建议我也尝试将命令类型设置为文本并使用以下语句，但我得到了无效的 SQL 语句
mypackage.getquestionsForPrint('OMG Poinies');
and
和
var r refcursor; exec :r := mypackage.getquestionsForPrint('OMG Poinies');
Using Abhi's variation for the command text
对命令文本使用 Abhi 的变体
select mypackage.getquestionsForPrint('OMG Poinies') from dual
resulted in
导致
The instruction at "0x61c4aca5" referenced memory at "0x00000ce1". The memory could not be "read".
“0x61c4aca5”处的指令引用了“0x00000ce1”处的内存。无法“读取”内存。
Am I just barking up the wrong tree?
我只是在吠错树吗？
UpdateAttempting to add an output parameter doesn't help.
更新尝试添加输出参数无济于事。
cmd.Parameters.Add(null, OracleDbType.RefCursor, ParameterDirection.Output);
Not sure what the name should besince its the return value of a function (I've tried null, empty string, mypackage.getquestionsForPrint) but in all cases it just results in
不确定名称应该是什么，因为它是函数的返回值（我试过 null、空字符串、mypackage.getquestionsForPrint）但在所有情况下它只会导致
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'getquestionsForPrint'
ORA-06550：第 1 行，第 7 列：PLS-00306：调用“getquestionsForPrint”时参数数量或类型错误
Final Edit (hopefully)
最终编辑（希望如此）
Apparently Guddie asked a similar question3 months after I did. He got the answer which is to
显然，Guddie在我问了3 个月后问了类似的问题。他得到的答案是
- Set your command text to an anonymous block
- Bind a parameter to the ref cursor setting the direction to output
- Call Execute non reader.
- Then use your parameter
- 将命令文本设置为匿名块
- 将参数绑定到 ref cursor 设置输出方向
- 调用执行非读取器。
- 然后使用你的参数
using (OracleConnection cnn = new OracleConnection("Data Source=Test;User Id=Test;Password=Test;"))
{
cnn.Open();
OracleCommand cmd = new OracleCommand("mypackage.getquestionsForPrint");
cmd.CommandType = CommandType.Text;
cmd.CommandText = "begin " +
" :refcursor1 := mypackage.getquestionsForPrint('OMG Ponies') ;" +
"end;";
cmd.Connection = cnn;
OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();
Oracle.DataAccess.Types.OracleRefCursor t = (Oracle.DataAccess.Types.OracleRefCursor)cmd.Parameters[0].Value;
OracleDataReader rdr = t.GetDataReader();
while(rdr.Read())
Console.WriteLine(rdr.GetOracleValue(0));
Console.ReadLine();
}
采纳答案by Peter C
I have not tested this with a function, but for my stored procedures. I specify the out parameter for the refCursor.
我没有用函数测试过这个，但是我的存储过程。我为 refCursor 指定了 out 参数。
command.Parameters.Add(new OracleParameter("refcur_questions", OracleDbType.RefCursor, ParameterDirection.Output));
If you are able to get the function to work with the CommandType.Text. I wonder if you can try adding the parameter above except with the direction as:
如果您能够使该函数与 CommandType.Text 一起使用。我想知道您是否可以尝试添加上面的参数，除了方向为：
ParameterDirection.ReturnValue
I am using Oracle.DataAccess version 2.111.6.0
我使用的是 Oracle.DataAccess 版本 2.111.6.0
回答by th1rdey3
I had to go up and down between the question and answers to figure out the full code that works. So I am giving the full code here that worked for me for others -
我不得不在问题和答案之间上下移动，以找出有效的完整代码。所以我在这里提供对其他人有用的完整代码 -
var sql = @"BEGIN :refcursor1 := mypackage.myfunction(:param1) ; end;";
using(OracleConnection con = new OracleConnection("<connection string>"))
using(OracleCommand com = new OracleCommand())
{
com.Connection = con;
con.Open();
com.Parameters.Add(":refcursor1", OracleDbType.RefCursor, ParameterDirection.Output);
com.Parameters.Add(":param1", "param");
com.CommandText = sql;
com.CommandType = CommandType.Text;
com.ExecuteNonQuery();
OracleRefCursor curr = (OracleRefCursor)com.Parameters[0].Value;
using(OracleDataReader dr = curr.GetDataReader())
{
if(dr.Read())
{
var value1 = dr.GetString(0);
var value2 = dr.GetString(1);
}
}
}
Hope it helps.
希望能帮助到你。
回答by th1rdey3
I know this is quite an old post, but since it took me so long to figure out all of the minutia involved in getting .NET to "fight nice" with Oracle, I figured I'd put this advice out there for anyone else in this sticky situation.
我知道这是一篇很老的帖子，但由于我花了很长时间才弄清楚让 .NET 与 Oracle“打得很好”有关的所有细节，我想我会把这个建议放在那里给其他人这种棘手的情况。
I frequently call Oracle stored procedures that return a REF_CURSOR in our environment (.NET 3.5 against Oracle 11g). For a function, you can indeed name the parameter anything you'd like, but then you need to set its System.Data.ParameterDirection= ParameterDirection.ReturnValuethen ExecuteNonQueryagainst the OracleCommandobject. At that point the value of that parameter will be the ref_cursor that the Oracle function returned. Just cast the value as an OracleDataReaderand loop through the OracleDataReader.
我经常调用在我们的环境中返回 REF_CURSOR 的 Oracle 存储过程（针对 Oracle 11g 的 .NET 3.5）。对于函数，您确实可以将参数命名为任何您想要的名称，但是您需要针对对象设置其System.Data.ParameterDirection= ParameterDirection.ReturnValuethen 。此时，该参数的值将是 Oracle 函数返回的 ref_cursor。只需将值转换为 an并循环遍历.ExecuteNonQueryOracleCommandOracleDataReaderOracleDataReader
I'd post the full code, but I wrote the data access layer in VB.NET years ago, and the bulk of the code consuming the data access layer (our corporate intranet) is in C#. I figured mixing languages in a single response would be the larger faux pas.
我会发布完整的代码，但几年前我用 VB.NET 编写了数据访问层，而消耗数据访问层（我们公司的内部网）的大部分代码是用 C# 编写的。我认为在单个响应中混合语言将是更大的失礼。
回答by Slider345
My guess is that you cannot call a function with a Command object of type 'StoredProcedure'. Can you try a Command object of type 'Text' and exec the function within the Command Text?
我的猜测是您不能使用类型为“StoredProcedure”的 Command 对象调用函数。您可以尝试使用“文本”类型的命令对象并在命令文本中执行该函数吗？
