Oracle RefCursor 的参数问题

发布于 2024-07-29 16:11:28 字数 1557 浏览 8 评论 0原文

我正在使用 ODP.NET（从 Microsoft 的提供商迁移），并且我陷入了返回引用游标的存储过程的困境。我有以下 PL/SQL 过程（我对其进行了一些更改以使其更通用）：

PROCEDURE MyProc(parameter_no1 IN NUMBER, parameter_no2 IN NUMBER, RETCURSOR OUT ret_type) AS
BEGIN
  OPEN RETCURSOR FOR
  SELECT   ad.logo logo 
  FROM    tab_a a, tab_h h 
  WHERE  a.id IS NOT NULL 
  AND    a.h_id = h.id 
  AND    a.no1 = parameter_no1
  AND    a.no2= parameter_no2;
END HanteraLogotype;

然后我有以下 C# 代码来调用它：

internal void RefCursorDataReader()
{
  OracleCommand cmd = new OracleCommand("ABC$MYPACKAGE.MyProc", new OracleConnection(_constr));
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection.Open();
  cmd.BindByName = true;

  OracleParameter p = cmd.Parameters.Add("parameter_no1", OracleDbType.Decimal);
  p.Value = 12345678;
  p.Direction = ParameterDirection.Input;

  p = cmd.Parameters.Add("parameter_no2", OracleDbType.Decimal);
  p.Value = 123456;
  p.Direction = ParameterDirection.Input;

  p = cmd.Parameters.Add("RETCURSOR", OracleDbType.RefCursor);
  p.Direction = ParameterDirection.Output;

  OracleDataReader reader = cmd.ExecuteReader();

  if (reader.Read())
  {
    System.Diagnostics.Debug.WriteLine(reader[0].GetType().ToString());
  }

  cmd.Connection.Close();
}

当我运行此过程时，我不断收到此异常：

ORA-03106: 致命的双任务通信协议错误

我尝试了许多不同的参数变体、它们的类型、顺序等，但似乎没有任何帮助。引发异常的是 reader.Read()。我非常感谢对此的帮助！

添加： ret_type 定义为：

TYPE ret_type 是参考游标；

原文

I'm using ODP.NET (migrating from Microsoft's provider), and I have got stuck on a stored procedure that returns a refcursor. I have the following PL/SQL procedure (I have changed it a little bit to make it more general):

PROCEDURE MyProc(parameter_no1 IN NUMBER, parameter_no2 IN NUMBER, RETCURSOR OUT ret_type) AS
BEGIN
  OPEN RETCURSOR FOR
  SELECT   ad.logo logo 
  FROM    tab_a a, tab_h h 
  WHERE  a.id IS NOT NULL 
  AND    a.h_id = h.id 
  AND    a.no1 = parameter_no1
  AND    a.no2= parameter_no2;
END HanteraLogotype;

And then I have the folloing C# code to call it:

internal void RefCursorDataReader()
{
  OracleCommand cmd = new OracleCommand("ABC$MYPACKAGE.MyProc", new OracleConnection(_constr));
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection.Open();
  cmd.BindByName = true;

  OracleParameter p = cmd.Parameters.Add("parameter_no1", OracleDbType.Decimal);
  p.Value = 12345678;
  p.Direction = ParameterDirection.Input;

  p = cmd.Parameters.Add("parameter_no2", OracleDbType.Decimal);
  p.Value = 123456;
  p.Direction = ParameterDirection.Input;

  p = cmd.Parameters.Add("RETCURSOR", OracleDbType.RefCursor);
  p.Direction = ParameterDirection.Output;

  OracleDataReader reader = cmd.ExecuteReader();

  if (reader.Read())
  {
    System.Diagnostics.Debug.WriteLine(reader[0].GetType().ToString());
  }

  cmd.Connection.Close();
}

And when I run this, I keep getting this exception:

ORA-03106: fatal two-task communication protocol error

I have tried numerous different variations of parameters, their type, order etc, but nothing seems to help. It's the reader.Read() that throws the exception. I would really appreciate assistance on this one!

Added:
the ret_type is defined as: