title: How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
body: <p>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.</p>
  
  <p>Here is the function header (obviously obfuscated):</p>
  
  <pre><code>FUNCTION GetXYZ(
     uniqueId       IN   somepackage.Number_Type,
     resultItems    OUT  somepackage.Ref_Type)
     RETURN somepackage.Error_Type;
  </code></pre>
  
  <p>These are the type definitions in "somepackage":</p>
  
  <pre><code>SUBTYPE Number_Type IS NUMBER(13);
  TYPE Ref_Type IS REF CURSOR;
  SUBTYPE Error_Type IS NUMBER;
  </code></pre>
  
  <p>And this is the code that I have tried:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The I tried the following different ways to call the function (of course only one at a time):</p>
  
  <pre><code>var result = getXYZCmd.ExecuteNonQuery();
  var reader = getXYZCmd.ExecuteReader();
  var scalarResult = getXYZCmd.ExecuteScalar();
  </code></pre>
  
  <p>But each of them fails with the error message:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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...</p>
  
  <p>Btw, I'm using ODP.NET version 2.112.2.0 from C#.NET 3.5 in Visual Studio 2008.</p>
  
  <p>Thanks in advance for your help!</p>