如何从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屋!