title: Returning a table of records from an Oracle function into a C# application
body: <p>We have an oracle package that returns a table of a record type. The definition is:</p>
  
  <pre><code>  TYPE t_daily_array_table IS TABLE OF r_daily_array_rec INDEX BY BINARY_INTEGER;
  </code></pre>
  
  <p>where <code>r_daily_array_rec</code> is a record with a number of fields in it. We then have a function in the package that's defined as follows:</p>
  
  <pre><code>FUNCTION f_daily_array_table
           (ip_contract_code IN contract.contract_code%TYPE)
  RETURN t_daily_array_table
  IS ...
  </code></pre>
  
  <p>And this all works. But I'd like to call this function from my C# application and I've tried about a hundred different variations using 2 different Oracle client drivers (the .NET OracleClient and Oracle's odp.net) and I can't seem to find a way to make it work.  </p>
  
  <p>I don't want to document all the different ways I've tried, but my latest iteration using the odp.net driver is this:</p>
  
  <pre><code>using (OracleConnection conn = new OracleConnection("Data Source=dbname;User Id=username;Password=password"))
  {
      using (OracleCommand cmd = new OracleCommand("FEED_SCHEDULE_PKG.f_daily_array_table", conn))
      {
          cmd.BindByName = true;
          cmd.Parameters.Add("ip_contract_code", 77116);
          cmd.CommandType = System.Data.CommandType.StoredProcedure;
          OracleParameter p = new OracleParameter("t_daily_array_table", OracleDbType.Varchar2);
          p.Direction = System.Data.ParameterDirection.ReturnValue;
          cmd.Parameters.Add(p);
          conn.Open();
          object ob = cmd.ExecuteNonQuery();
      }
  }
  </code></pre>
  
  <p>I've tried variations of SQL Statements, such as:</p>
  
  <p><code>select * from Table(FEED_SCHEDULE_PKG.f_daily_array_table(ip_contract_code));</code> 
  or
  <code>begin FEED_SCHEDULE_PKG.f_daily_array_table(ip_contract_code); end;</code></p>
  
  <p>I've tried <code>ExecuteNonQuery</code> with a return parameter. I've tried <code>ExecuteReader</code> into an <code>IDataReader</code>. I've tried 'returnvalue' for the return value parameter name. </p>
  
  <p>I've searched around and tried everything I can find. None of the example functions is quite like ours. Like I said, I've tried lots of variations. I can't seem to hit on the right setup. </p>
  
  <p>I'd really appreciate some help on this.</p>