Execute an oracle Function that returns a reference cursor in C#

Question

I have an oracle package with a procedure that has a in out reference cursor. My understanding is that this is pretty standard.

What I didn't like is the fact that I had to write a ton of code to just see the output. [So I asked this question][1] and it turns out I can get what I want by creating a function that wraps the procedure.

Update: 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.

Here's the function

FUNCTION GetQuestionsForPrint (user in varchar2)
  RETURN MYPACKAGE.refcur_question
AS  

    OUTPUT MYPACKAGE.refcur_question;

BEGIN 

      MYPACKAGE.GETQUESTIONS(p_OUTPUT => OUTPUT, 
      p_USER=> USER ) ;


  RETURN OUTPUT;
END;

and here's what I do execute in SQL Developer

var r refcursor;
exec :r := mypackage.getquestionsForPrint('OMG Ponies');
print r;

So from now on I'm probably going to add the ForPrint functions to all my procedures.

This got me thinking, maybe functions are what I want and I don't need procedures.

To test this I tried executing the function from .NET, except I can't do it. Is this really the way it is.

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();
}

So I get the error.

getquestionsForPrint is not a procedure or is undefined

I tried ExecuteScalar as well with the same result.

EDIT 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

mypackage.getquestionsForPrint('OMG Poinies');

and

var r refcursor; exec :r :=  mypackage.getquestionsForPrint(0,300,'0','0,',NULL,'ALL'); 

Am I just barking up the wrong tree?

Answer 1

My guess is that you cannot call a function with a Command object of type 'StoredProcedure'. Can you try a Command object of type 'Text' and exec the function within the Command Text?