title: Execute an oracle Function that returns a reference cursor in C#
body: <p>I have an oracle package with a procedure that has a in out reference cursor. My understanding is that this is pretty standard. </p>
  
  <p>What I didn't like is the fact that I had to write a ton of code to just see the output. <a href="https://stackoverflow.com/q/3526798/119477">So I asked this question</a> and it turns out I can get what I want by creating a function that wraps the procedure. </p>
  
  <p><strong>Update:</strong> Looks like I don't need the function anymore but it may be worth knowing anyway for those curious see the original question and answer updates.</p>
  
  <p>Here's the function</p>
  
  <pre><code>FUNCTION GetQuestionsForPrint (user in varchar2)
    RETURN MYPACKAGE.refcur_question
  AS  
  
      OUTPUT MYPACKAGE.refcur_question;
  
  BEGIN 
  
        MYPACKAGE.GETQUESTIONS(p_OUTPUT =&gt; OUTPUT, 
        p_USER=&gt; USER ) ;
  
  
    RETURN OUTPUT;
  END;
  </code></pre>
  
  <p>and here's what I do to execute it in SQL Developer</p>
  
  <pre><code>var r refcursor;
  exec :r := mypackage.getquestionsForPrint('OMG Ponies');
  print r;
  </code></pre>
  
  <p>So from now on I'm probably going to add the ForPrint functions to all my procedures.</p>
  
  <p>This got me thinking, maybe functions are what I want and I don't need procedures. </p>
  
  <p>To test this I tried executing the function from .NET, except I can't do it. Is this really the way it is.</p>
  
  <pre><code>using (OracleConnection cnn = new OracleConnection("Data Source=Test;User Id=Test;Password=Test;"))
  {
      cnn.Open();
      OracleCommand cmd = new OracleCommand("mypackage.getquestionsForPrint");
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
      cmd.Parameters.Add ( "p_USER", "OMG Ponies");
  
      cmd.Connection = cnn;
      OracleDataReader rdr = cmd.ExecuteReader();
  
      while (rdr.Read())
      {
          Console.WriteLine(rdr.GetOracleValue(0));
      }
  
      Console.ReadLine();
  }
  </code></pre>
  
  <p>So I get the error. </p>
  
  <p><code>getquestionsForPrint is not a procedure or is undefined</code></p>
  
  <p>I tried ExecuteScalar as well with the same result.</p>
  
  <p><strong>EDIT</strong> Taking Slider345's advice I've also tried setting the command type to text and using the following statement and I get 
   invalid SQL statement</p>
  
  <pre><code>mypackage.getquestionsForPrint('OMG Poinies');
  </code></pre>
  
  <p>and </p>
  
  <pre><code>var r refcursor; exec :r :=  mypackage.getquestionsForPrint('OMG Poinies'); 
  </code></pre>
  
  <p>Using Abhi's variation for the command text </p>
  
  <pre><code>select mypackage.getquestionsForPrint('OMG Poinies') from dual
  </code></pre>
  
  <p>resulted in </p>
  
  <blockquote>
    <p>The instruction at "0x61c4aca5"
    referenced memory at "0x00000ce1". The
    memory could not be "read".</p>
  </blockquote>
  
  <p>Am I just barking up the wrong tree?</p>
  
  <p><strong><em>Update</em></strong>
  Attempting to add an output parameter doesn't help. </p>
  
  <pre><code>cmd.Parameters.Add(null, OracleDbType.RefCursor, ParameterDirection.Output);
  </code></pre>
  
  <p>Not sure what the <strong><em>name should be</em></strong> since its the return value of a function (I've tried null, empty string, mypackage.getquestionsForPrint) but in all cases it just results in </p>
  
  <blockquote>
    <p>ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of
    arguments in call to
    'getquestionsForPrint'</p>
  </blockquote>
  
  <p><strong>Final Edit (hopefully)</strong></p>
  
  <p>Apparently Guddie asked a <a href="https://stackoverflow.com/q/4125992/119477">similar question</a> 3 months after I did. He got the answer which is to </p>
  
  <ul>
  <li>Set your command text to an anonymous block</li>
  <li>Bind a parameter to the ref cursor setting the direction to output</li>
  <li>Call Execute non reader. </li>
  <li>Then use your parameter</li>
  </ul>
  
  <hr>
  
  <pre><code>using (OracleConnection cnn = new OracleConnection("Data Source=Test;User Id=Test;Password=Test;"))
  {
      cnn.Open();
      OracleCommand cmd = new OracleCommand("mypackage.getquestionsForPrint");
      cmd.CommandType = CommandType.Text;
  
      cmd.CommandText = "begin " +
                "    :refcursor1 := mypackage.getquestionsForPrint('OMG Ponies') ;"  +
                "end;";
  
      cmd.Connection = cnn;
      OracleDataAdapter da = new OracleDataAdapter(cmd);
      cmd.ExecuteNonQuery();
  
      Oracle.DataAccess.Types.OracleRefCursor t = (Oracle.DataAccess.Types.OracleRefCursor)cmd.Parameters[0].Value;
      OracleDataReader rdr = t.GetDataReader();
      while(rdr.Read())
          Console.WriteLine(rdr.GetOracleValue(0));
  
      Console.ReadLine();
  }
  </code></pre>