如何从Oracle函数返回RefCursor？ [英] How to return a RefCursor from Oracle function?
问题描述
我试图执行用户定义的Oracle函数，该函数使用ODP.NET返回RefCursor。这是函数：
pre $ CREATE OR REPLACE FUNCTION PKG.FUNC_TEST（ID IN TABLE.ID％类型）
RETURN SYS_REFCURSOR
AS
REF_TEST SYS_REFCURSOR;
BEGIN
OPEN REF_TEST FOR
SELECT *
FROM TABLE;
RETURN REF_TEST;
END;
/
我可以在Toad中调用这个函数（从double中选择func_test（7）并找回一个CURSOR。但我需要使用C＃和ODP.NET来获取游标以填充DataSet，但我一直在收到NullReferenceException - 未将对象引用设置为对象的实例。这里是我所拥有的：
OracleConnection oracleCon = new OracleConnection（ConfigurationManager.ConnectionStrings [MyConnectionString]。ConnectionString）;
OracleCommand sqlCom = new OracleCommand（select double func_test（7），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
我能够在存储过程中找到大量信息和示例， ODP.NET，但不是用于从函数返回RefCursors。
编辑：我不想显式地将输入参数添加到OracleCommand对象（即 sqlCom.Parameters.Add（id，OracleDbType.Int32，ParameterDirection.Input）.Value = 7; ），因为这很难实现这个通用的REST风格的Web服务，但我保留它作为我最后的手段，但会使用存储过程。
任何帮助都非常感谢！
我认为您缺少 sqlCom.ExecuteNonQuery（）;
，而不是运行从双重选择func_test（7）; 让它切换到运行该函数并传递参数
OracleConnec oracleCon = new OracleConnection（ConfigurationManager.ConnectionStrings [MyConnectionString]。ConnectionString）;
//设置命令
字符串anonymous_block =开始+
：refcursor1：= func_test（7）; +
end;;
//通过上面的例子填写你的函数和变量
OracleCommand sqlCom = con.CreateCommand（）;
sqlCom.CommandText = anonymous_block;
//绑定
sqlCom.Parameters.Add（refcursor1，OracleDbType.RefCursor）;
sqlCom.Parameters [0] .Direction = ParameterDirection.ReturnValue;
尝试
{
//执行命令;让参数填充
sqlCom.ExecuteNonQuery（）;
//创建OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter（sqlCom）;
//用refcursor1填充数据集。
DataSet ds = new DataSet（）;
da.Fill（ds，refcursor1，（OracleRefCursor）（sqlCom.Parameters [refcursor1]。Value））;
//打印字段计数REF Cursor
Console.WriteLine（Field count：+ ds.Tables [refcursor1]。Columns.Count）;
catch（Exception e）
{
Console.WriteLine（Error：{0}，e.Message）;
}
finally
{
//处理OracleCommand对象
cmd.Dispose（）;
//关闭并处理OracleConnection对象
con.Close（）;
con.Dispose（）;}
这是基于示例ODP，可以找到@％ora_home％\Client_1\ODP.NET\samples\RefCursor\Sample5.csproj
如果您想避免（好或坏！ ）为每个proc / function调用定制的param集合，您可以通过在代码中使用匿名块来解决这个问题，我已经推荐（再次未经测试！）上面的代码来反映这种技术。
这是一个很好的博客（来自Mark Williams）展示了这种技巧。
http：//oradim.blogspot。 com / 2007/04 / odpnet-tip-anonymous-plsql-and.html
I am trying to execute a user-defined Oracle function that returns a RefCursor using ODP.NET. Here is the function:
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:
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.
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.
Any help is much appreciated!
I think you are missing the 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
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
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
这篇关于如何从Oracle函数返回RefCursor？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
