如何使用Ref Cursor作为Out参数从C＃中调用Oracle函数？ [英] How to call an Oracle function with a Ref Cursor as Out-parameter from C#?

问题描述

我正在使用提供基于Oracle函数的数据库API的产品，并且我可以通过ODP.NET调用函数。但是，我无法弄清楚，如何调用一个包含Ref Cursor作为Out参数的函数。到目前为止，我发现的所有样本或者使用Out参数调用过程，或者使用Ref Cursor作为返回值调用函数。我试着类似地定义参数，但不断收到错误提供了错误的数字或参数类型。

这里是函数头（显然是模糊的）：

 函数GetXYZ（
 uniqueId IN somepackage.Number_Type，
 resultItems OUT somepackage.Ref_Type）
返回somepackage.Error_Type; 
  

以下是somepackage中的类型定义：

  SUBTYPE Number_Type IS NUMBER（13）; 
 TYPE Ref_Type IS REF CURSOR; 
 SUBTYPE Error_Type IS NUMBER; 
  

这是我试过的代码：

  string sql =otherpackage.GetXYZ; 
 var getXYZCmd = OracleCommand oracleConnection.CreateCommand（sql）; 
 getXYZCmd.CommandType = CommandType.StoredProcedure; 
 
 getXYZCmd.Parameters.Add（uniqueId，OracleDbType.Int32）.Value = uniqueExplosionId; 
 getXYZCmd.Parameters.Add（resultItems，OracleDbType.RefCursor）.Direction = ParameterDirection.Output; 
 getXYZCmd.Parameters.Add（return_value，OracleDbType.Int32）.Direction = ParameterDirection.ReturnValue; 
  

我尝试了以下不同的方式来调用函数（当然每次只有一个）：

  var result = getXYZCmd.ExecuteNonQuery（）; 
 var reader = getXYZCmd.ExecuteReader（）; 
 var scalarResult = getXYZCmd.ExecuteScalar（）; 
  

但它们每个都会失败并显示错误消息：

  Oracle.DataAccess.Client.OracleException：ORA-06550：第1行第15列：
 PLS-00306：调用'GETXYZ时参数的数量或类型错误'
 ORA-06550：第1行第15列：
 PLS-00306：调用'GETXYZ'时参数的数量或类型错误
 ORA-06550：第1行第7列：
 PL / SQL：语句被忽略。 
  

因此，通常可以使用Ref Cursor作为Out参数从C＃调用带ODP的函数。净？我可以用一个Varchar2-Out参数代替Ref Cursor调用一个具有相同结构的函数，而不会出现问题...

顺便说一句，我使用的是ODP.NET版本2.112.2.0从Visual Studio 2008中的C＃.NET 3.5开始。

预先感谢您的帮助！

解决方案

你当然可以。有几个小问题要警惕，但这里是一个测试用例

 创建或替换函数testodpRefCursor（
 uniqueId IN NUMBER 
，resultItems OUT NOCOPY SYS_REFCURSOR）RETURN NUMBER 
 IS 
 $ b BEGIN 
 OPEN resultItems用于从级别双连接中选择级别<唯一身份 ; 
返回1; 
 END testodpRefCursor; 
  

1. 我发现
   函数喜欢使
   ReturnValue为 第一 $ pa
   
   BindByName默认为FALSE，所以它默认为BIND BY POSITION

否则它非常简单：

pre $ OracleCommand cmd = new OracleCommand（TESTODPREFCURSOR，con ）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
//绑定


OracleParameter oparam = cmd.Parameters.Add（ReturnValue，OracleDbType.Int64）;
oparam.Direction = ParameterDirection.ReturnValue;

OracleParameter oparam0 = cmd.Parameters.Add（uniqueId，OracleDbType.Int64）;
oparam0.Value = 5;
oparam0.Direction = ParameterDirection.Input;

OracleParameter oparam1 = cmd.Parameters.Add（resultItems，OracleDbType.RefCursor）;
oparam1.Direction = ParameterDirection.Output;




//执行命令
OracleDataReader reader;
尝试
{
reader = cmd.ExecuteReader（）; （reader.Read（））{
Console.WriteLine（level：{0}，reader.GetDecimal（0））;

while
}

} ...


现在查看更多示例转到您的Oracle Home目录，查看@ ODP.NET中的Ref cursor示例。

例如：
％oracle client home％\odp.net\\ \\ samples \ 4 \RefCursor

I'm using a product that provides a database API based on Oracle functions and I'm able to call functions via ODP.NET in general. However, I can't figure out, how to call a function that includes a Ref Cursor as Out-parameter. All the samples I found so far either call a procedure with Out-parameter or a function with the Ref Cursor as return value. I tried to define the parameters similiarly, but keep getting the error that the wrong number or type of parameters is supplied.

Here is the function header (obviously obfuscated):

FUNCTION GetXYZ(
   uniqueId       IN   somepackage.Number_Type,
   resultItems    OUT  somepackage.Ref_Type)
   RETURN somepackage.Error_Type;

These are the type definitions in "somepackage":

SUBTYPE Number_Type IS NUMBER(13);
TYPE Ref_Type IS REF CURSOR;
SUBTYPE Error_Type IS NUMBER;

And this is the code that I have tried:

string sql = "otherpackage.GetXYZ";
var getXYZCmd = OracleCommand oracleConnection.CreateCommand(sql);
getXYZCmd.CommandType = CommandType.StoredProcedure;

getXYZCmd.Parameters.Add("uniqueId", OracleDbType.Int32).Value = uniqueExplosionId;
getXYZCmd.Parameters.Add("resultItems", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
getXYZCmd.Parameters.Add("return_value", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue;

The I tried the following different ways to call the function (of course only one at a time):

var result = getXYZCmd.ExecuteNonQuery();
var reader = getXYZCmd.ExecuteReader();
var scalarResult = getXYZCmd.ExecuteScalar();

But each of them fails with the error message:

Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'GETXYZ'
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'GETXYZ'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

So is it generally possible to call a function with a Ref Cursor as Out-parameter from C# with ODP.NET? I can call a function with the same structure with a Varchar2-Out-parameter instead of the Ref Cursor without problems...

Btw, I'm using ODP.NET version 2.112.2.0 from C#.NET 3.5 in Visual Studio 2008.

Thanks in advance for your help!

解决方案

You sure can. There are a few gotchas to be wary of but here is a test case

create or replace function testodpRefCursor(
                  uniqueId    IN NUMBER 
                 ,resultItems OUT NOCOPY SYS_REFCURSOR) RETURN NUMBER
                 IS

 BEGIN
      OPEN resultItems for select level from dual  connect by level < uniqueId ;
      return 1;
 END testodpRefCursor;

1. I have found that functions likes to have the ReturnValue as THE FIRST param in the collection
2. BindByName is by default FALSE, so it defaults to BIND BY POSITION

Otherwise it is quite straight forward:

  OracleCommand cmd = new OracleCommand("TESTODPREFCURSOR", con);
  cmd.CommandType   = CommandType.StoredProcedure;
  cmd.BindByName = true;
  // Bind 


  OracleParameter oparam = cmd.Parameters.Add("ReturnValue", OracleDbType.Int64);
  oparam.Direction = ParameterDirection.ReturnValue ;       

  OracleParameter oparam0 = cmd.Parameters.Add("uniqueId", OracleDbType.Int64);
  oparam0.Value = 5 ;
  oparam0.Direction = ParameterDirection.Input;

  OracleParameter oparam1 = cmd.Parameters.Add("resultItems", OracleDbType.RefCursor);
  oparam1.Direction = ParameterDirection.Output;




  // Execute command
  OracleDataReader reader;
  try
  {
    reader = cmd.ExecuteReader();

    while(reader.Read() ){
        Console.WriteLine("level: {0}", reader.GetDecimal(0));  
    }

  } ...

Now for more samples go to your Oracle Home directory and look @ the Ref cursor samples in ODP.NET

for instance: %oracle client home%\odp.net\samples\4\RefCursor

hth

这篇关于如何使用Ref Cursor作为Out参数从C＃中调用Oracle函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！