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 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 to execute it 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('OMG Poinies'); 

Using Abhi's variation for the command text

select mypackage.getquestionsForPrint('OMG Poinies') from dual

resulted in

The instruction at "0x61c4aca5" referenced memory at "0x00000ce1". The memory could not be "read".

Am I just barking up the wrong tree?

Update Attempting to add an output parameter doesn't help.

cmd.Parameters.Add(null, OracleDbType.RefCursor, ParameterDirection.Output);

Not sure what the name should be since its the return value of a function (I've tried null, empty string, mypackage.getquestionsForPrint) but in all cases it just results in

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'getquestionsForPrint'

Final Edit (hopefully)

Apparently Guddie asked a similar question 3 months after I did. He got the answer which is to

• Set your command text to an anonymous block
• Bind a parameter to the ref cursor setting the direction to output
• Call Execute non reader.
• Then use your parameter

---

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

Answer 1

I have not tested this with a function, but for my stored procedures. I specify the out parameter for the refCursor.

command.Parameters.Add(new OracleParameter("refcur_questions", OracleDbType.RefCursor, ParameterDirection.Output));

If you are able to get the function to work with the CommandType.Text. I wonder if you can try adding the parameter above except with the direction as:

ParameterDirection.ReturnValue

I am using Oracle.DataAccess version 2.111.6.0