title: C# - How to return a cursor from an Oracle procedure inside a package
body: <h1>Package</h1>
  
  <h3>Package Declaration</h3>
  
  <p>This is the package declaration (<em>Oracle 9i</em>):</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE
  PACKAGE MyPackage AS
  
      PROCEDURE Results(start_date IN DATE, end_date IN DATE, brand IN NUMBER, summary OUT SYS_REFCURSOR);
  
  END MyPackage;
  </code></pre>
  
  <h3>Package Body</h3>
  
  <p>This is the package body with the procedure:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE
  PACKAGE BODY MyPackage AS
  
      PROCEDURE Results(start_date IN DATE, end_date IN DATE, brand IN NUMBER, summary OUT SYS_REFCURSOR) IS
          BEGIN
          OPEN summary FOR
              SELECT NVL(id, 0), type, order, SUM(alpha), SUM(beta), SUM(gamma), SUM(delta), SUM(epsilon), SUM(zeta), SUM(eta), SUM(theta), SUM(iota), SUM(kappa), SUM(lambda)
              FROM sample_owner.sample_table
              WHERE fecha BETWEEN start_date AND end_date AND id = brand
              GROUP BY id, type, order
              ORDER BY id, type, order;
          END Results;
  
  END MyPackage;
  </code></pre>
  
  <h1>Code</h1>
  
  <p>Here is how I try to retrieve the cursor in C# (using <em>System.Data.OracleClient</em> in <em>.NET 2.0</em>):</p>
  
  <pre class="lang-cs prettyprint-override"><code>public static DataSet getResults(DateTime beginning, DateTime ending, int myBrand)
  {
  
      string cs = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservicename)));User ID=cat;Password=dog;Persist Security Info=True";
      OracleConnection conn = new OracleConnection(cs);
      OracleCommand cmd = conn.CreateCommand();
  
      cmd.CommandText = "MyPackage.Results";
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.Parameters.Add("start_date", OracleType.DateTime).Direction = System.Data.ParameterDirection.Input;
      cmd.Parameters["start_date"].Value = beginning;
      cmd.Parameters.Add("end_date", OracleType.DateTime).Direction = System.Data.ParameterDirection.Input;
      cmd.Parameters["end_date"].Value = ending;
      cmd.Parameters.Add("brand", OracleType.Int32).Direction = System.Data.ParameterDirection.Input;
      cmd.Parameters["brand"].Value = myBrand;
      cmd.Parameters.Add("summary", OracleType.Cursor).Direction = System.Data.ParameterDirection.Output;
  
      try
      {
          conn.Open();
          cmd.ExecuteNonQuery();
          DataSet ds = new DataSet("testDS");
          new OracleDataAdapter(cmd).Fill(ds);
          cmd.Parameters.Clear();
          conn.Close();
          return ds;
      }
      catch (Exception ex)
      {
          ex.ToString();
          return null;
      }
  
  }
  </code></pre>
  
  <p>And here are the values that I'm using to make the call:</p>
  
  <pre class="lang-cs prettyprint-override"><code>int myBrand = 1;
  DateTime beginning = new DateTime(2014, 1, 1);
  DateTime ending = new DateTime(2014, 1, 31);
  DataSet myDataSet = getResults(beginning, ending, myBrand);
  </code></pre>
  
  <p>The dataset returns empty, and I'm not receiving any error messages.</p>
  
  <p>What am I doing wrong?</p>