如何从 Oracle 函数返回 RefCursor？

Question

我正在尝试执行用户定义的 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;
/

我可以在 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

我能够找到大量有关使用存储过程和 ODP.NET 的信息和示例，但对于从函数返回 RefCursors 的信息和示例却没有那么多。

编辑： 我不想向 OracleCommand 对象显式添加输入参数（即 sqlCom.Parameters.Add("id", OracleDbType.Int32,ParameterDirection.Input).Value = 7; ），因为这使得将其实现为通用 RESTful Web 服务变得困难，但我将其保留为最后的手段，但会使用存储过程。

非常感谢任何帮助！

Answer 1

我认为您还缺少 sqlCom.ExecuteNonQuery();

，而不是从 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();}

基于可以在 @%ora_home%\Client_1\ODP.NET\samples\RefCursor\Sample5.csproj 找到的示例 ODP

如果您想避免（无论是好是坏！）每个自定义构建的参数集合proc/函数调用，您可以通过在代码中使用匿名块来解决这个问题，我修改了（再次未经测试！）上面的代码以反映这种技术。
这是一个很好的博客（来自 Mark Williams）展示了这种技术。
http://oradim.blogspot.com/2007/ 04/odpnet-tip-anonymous-plsql-and.html