Oracle RefCursor的参数问题 [英] Parameter issue with Oracle RefCursor

问题描述

我正在使用ODP.NET(从Microsoft的提供程序迁移)，并且卡在了一个返回引用的存储过程中.我有以下PL/SQL过程(为了使它更加通用，我对其进行了一些更改):

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;

然后我有下面的C#代码来调用它:

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:致命的两任务通信协议错误

ORA-03106: fatal two-task communication protocol error

我尝试了许多参数，类型，顺序等各种变体，但似乎无济于事.引发异常的是reader.Read().我真的很感谢您的协助！

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!

已添加: ret_type定义为:

Added: the ret_type is defined as:

TYPE ret_type是参考光标；

TYPE ret_type IS REF CURSOR;

推荐答案

这似乎是一个错误. 3106错误是一个绝对不会发生的严重错误.我确定有解决方法！

That looks like a bug. The 3106 error is a bad error that should never happen. I'm sure there's a workaround though!!

问ODP.NET问题的最佳地点是OTN ODP.NET论坛.如果我是你，我会把它张贴在那儿:

The best place to ask ODP.NET questions is over on the OTN ODP.NET forum. If I were you I would post this over there:

http://forums.oracle.com/forums /forum.jspa?forumID=146&start=0

还在该特定论坛中搜索"3106"

Also search that particular forum for "3106"

这篇关于Oracle RefCursor的参数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！