C# 执行返回引用游标的 oracle 函数

Question

我有一个 oracle 包，其中的过程有一个输入输出引用游标。我的理解是，这是相当标准的。

我不喜欢的是我必须编写大量代码才能看到输出。 所以我问了这个问题，事实证明我可以通过创建一个包装该过程的函数来得到我想要的东西。

更新：看起来我不再需要该功能，但对于那些好奇的人来说，查看原始问题和答案更新可能还是值得了解的。

这是该函数

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;

，以及我在 SQL Developer 中执行该函数的操作。

var r refcursor;
exec :r := mypackage.getquestionsForPrint('OMG Ponies');
print r;

因此，从现在开始，我可能会将 ForPrint 函数添加到我的所有过程中。

这让我想到，也许函数就是我想要的，我不需要程序。

为了测试这一点，我尝试从 .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();
}

所以我得到了错误。

getquestionsForPrint 不是过程或未定义

我也尝试了 ExecuteScalar，但结果相同。

编辑采纳Slider345的建议，我也尝试将命令类型设置为文本并使用以下语句，我得到 无效的 SQL 语句

mypackage.getquestionsForPrint('OMG Poinies');

和

var r refcursor; exec :r :=  mypackage.getquestionsForPrint('OMG Poinies'); 

使用 Abhi 的命令文本变体

select mypackage.getquestionsForPrint('OMG Poinies') from dual

导致

“0x61c4aca5”处的指令 引用的内存位于“0x00000ce1”。这 内存无法“读取”。

我是不是找错了树？

更新 尝试添加输出参数没有帮助。

cmd.Parameters.Add(null, OracleDbType.RefCursor, ParameterDirection.Output);

不确定名称应该是什么，因为它是函数的返回值（我尝试过 null、空字符串、mypackage.getquestionsForPrint），但在所有情况下它都只是结果在

ORA-06550: 第 1 行，第 7 列： PLS-00306：数量或类型错误 调用中的参数 'getquestionsForPrint'

最终编辑（希望）

显然，Guddie 在我提出问题 3 个月后提出了一个类似问题 。他得到的答案是将

• 命令文本设置为匿名块，
• 方向
• 将参数绑定到引用光标，设置输出调用执行非读取器的
• 。然后使用你的参数

---

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();
}

Answer 1

我必须在问题和答案之间来回查找才能找出有效的完整代码。因此，我在这里给出对其他人有用的完整代码 -

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);
         }
     }
 }

希望它有所帮助。

Answer 2

我知道这是一篇相当老的文章，但由于我花了很长时间才弄清楚让 .NET 与 Oracle“友好地战斗”所涉及的所有细节，我想我应该将这个建议提供给其他人这种棘手的情况。

我经常调用在我们的环境（针对 Oracle 11g 的 .NET 3.5）中返回 REF_CURSOR 的 Oracle 存储过程。对于函数，您确实可以将参数命名为任何您想要的名称，但是您需要设置其 System.Data.ParameterDirection = ParameterDirection.ReturnValue 然后 针对 OracleCommand 对象执行NonQuery。此时该参数的值将是 Oracle 函数返回的 ref_cursor。只需将值转换为 OracleDataReader 并循环遍历 OracleDataReader。

我本来会发布完整的代码，但几年前我用 VB.NET 编写了数据访问层，而使用数据访问层（我们的公司内部网）的大部分代码都是用 C# 编写的。我认为在一个响应中混合语言将是更大的失礼。

Answer 3

我没有用函数测试过这个，而是针对我的存储过程。我为 refCursor 指定了 out 参数。

command.Parameters.Add(new OracleParameter("refcur_questions", OracleDbType.RefCursor, ParameterDirection.Output));

如果您能够使该函数与 CommandType.Text 一起使用。我想知道您是否可以尝试添加上面的参数，除了方向：

ParameterDirection.ReturnValue

我正在使用 Oracle.DataAccess 版本 2.111.6.0