如何从 Oracle 函数返回 RefCursor？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4125992/
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 return a RefCursor from Oracle function?
提问by Gady
I am trying to execute a user-defined Oracle function that returns a RefCursor using ODP.NET. Here is the function:
我正在尝试执行一个用户定义的 Oracle 函数，该函数使用 ODP.NET 返回一个 RefCursor。这是函数：
CREATE OR REPLACE FUNCTION PKG.FUNC_TEST (ID IN TABLE.ID%type)
RETURN SYS_REFCURSOR
AS
REF_TEST SYS_REFCURSOR;
BEGIN
OPEN REF_TEST FOR
SELECT *
FROM TABLE;
RETURN REF_TEST;
END;
/
I can call this function in Toad (select func_test(7) from dual) and get back a CURSOR. But I need to get the cursor using C# and ODP.NET to fill a DataSet, but I keep getting a NullReferenceException - "Object reference not set to an instance of an object". Here is what I have for that:
我可以在 Toad 中调用此函数（从 dual 中选择 func_test(7)）并返回一个 CURSOR。但是我需要使用 C# 和 ODP.NET 来获取游标来填充数据集，但是我不断收到 NullReferenceException -“未将对象引用设置为对象的实例”。这是我所拥有的：
OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
OracleCommand sqlCom = new OracleCommand("select func_test(7) from dual", oracleCon);
sqlCom.Parameters.Add("REF_TEST", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
OracleDataAdapter dataAdapter = new OracleDataAdapter();
dataAdapter.SelectCommand = sqlCom;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet); //FAILS HERE with NullReferenceException
I was able to find lots of info and samples on using stored procedures and ODP.NET, but not so much for returning RefCursors from functions.
我能够找到很多关于使用存储过程和 ODP.NET 的信息和示例，但对于从函数返回 RefCursors 却没有那么多。
EDIT:I do not want to explicitly add input parameters to the OracleCommand object (i.e. sqlCom.Parameters.Add("id", OracleDbType.Int32,ParameterDirection.Input).Value = 7;) as that makes it difficult to implement this as a generic RESTful web service, but I'm reserving it as my last resort but would use stored procedures instead.
编辑：我不想将输入参数显式添加到 OracleCommand 对象（即sqlCom.Parameters.Add("id", OracleDbType.Int32,ParameterDirection.Input).Value = 7;），因为这使得很难将其实现为通用的 RESTful Web 服务，但我将其保留为最后的手段，但会使用存储过程。
Any help is much appreciated!
任何帮助深表感谢！
回答by Harrison
I think you are missing the sqlCom.ExecuteNonQuery();
我认为您缺少sqlCom.ExecuteNonQuery();
also, instead of running the select func_test(7) from dual;lets switch it to run the function and pass in the param
另外，不是从 dual运行select func_test(7) ；让我们切换它来运行函数并传入参数
OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
// Set the command
string anonymous_block = "begin " +
" :refcursor1 := func_test(7) ;" +
"end;";
//fill in your function and variables via the above example
OracleCommand sqlCom= con.CreateCommand();
sqlCom.CommandText = anonymous_block;
// Bind
sqlCom.Parameters.Add("refcursor1", OracleDbType.RefCursor);
sqlCom.Parameters[0].Direction = ParameterDirection.ReturnValue;
try
{
// Execute command; Have the parameters populated
sqlCom.ExecuteNonQuery();
// Create the OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(sqlCom);
// Populate a DataSet with refcursor1.
DataSet ds = new DataSet();
da.Fill(ds, "refcursor1", (OracleRefCursor)(sqlCom.Parameters["refcursor1"].Value));
// Print out the field count the REF Cursor
Console.WriteLine("Field count: " + ds.Tables["refcursor1"].Columns.Count);
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();}
this is based on the example ODP that can be found @ %ora_home%\Client_1\ODP.NET\samples\RefCursor\Sample5.csproj
这是基于可以在@%ora_home%\Client_1\ODP.NET\samples\RefCursor\Sample5.csproj 中找到的示例 ODP
If you want to avoid (for better or worst!) the custom built param collection for each proc/function call you can get around that by utilizing anonymous blocks in your code, I have ammended (once again untested!) the code above to reflect this technique. Here is a nice blog (from none other than Mark Williams) showing this technique. http://oradim.blogspot.com/2007/04/odpnet-tip-anonymous-plsql-and.html
如果您想避免（无论好坏！）每个 proc/函数调用的自定义构建参数集合，您可以通过在代码中使用匿名块来解决这个问题，我已经修改（再次未经测试！）上面的代码以反映这种技术。这是一个很好的博客（来自 Mark Williams）展示了这种技术。 http://oradim.blogspot.com/2007/04/odpnet-tip-anonymous-plsql-and.html
