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

Conrad, you don't need the wrapper - I updated the earlier question. Don't know C# so can't comment on that part. Personally - and I'm sure many people would shoot me down on this - I'd say generally that a procedure should do some amount of work and can have an impact both on data that is and isn't passed in (which you don't necessarily know about), and a function should return a result based on passed-in data and preferably not have any side-effects. But it's whatever is most suitable for what you're doing. Going back and changing old code is unlikely to add much, I'd have thought. — Alex Poole, Aug 22 '10 at 21:15
I have faced similar difficulties. So I had to resort changing my functions to stored procedures. I did not answer since this question is old. Changing the CommandType suggestion given by Slider345 does not work if your function is returning a REFCURSOR. It will work if you have a scalar function, which returns a value and not a recordset. In that case too, you have to use your function in a query like "SELECT function_name FROM DUAL". Perhaps you could try this approach on the above function. CHange your commandtype to TEXT and then change the syntax to call the function. — abhi, Feb 14 '11 at 21:32
Did you see the "Test the REF CURSOR Procedure from within C#" section on [this link](http://www.akadia.com/services/ora_return_result_set.html) -- half way down the page for me. — OMG Ponies, Feb 19 '11 at 01:28
@OMG Ponies. That works great for `PROCEDURE`s. For `FUNCTION`s not so much. — Conrad Frix, Feb 22 '11 at 16:24
@abhi. Please feel free to answer old questions see this [blog entry](http://blog.stackoverflow.com/2010/11/the-horror-of-no-answer-revival-and-necromancer/). Sadly when I tried your approach, it resulted in a weird memory error. — Conrad Frix, Feb 22 '11 at 16:35
@Conrad, I think I have fix for you. I posted a very similar question in Oracle Forums, where someone pointed out the correct way of doing this. It has worked for me, so it shuold work for yuo as well. Check this link. http://forums.oracle.com/forums/message.jspa?messageID=9369507#9369507 — abhi, Feb 23 '11 at 20:30
@Kiquenet what do you feel is missing from the block below the "Final Edit" or this [answer](http://stackoverflow.com/a/4126886/119477) — Conrad Frix, Aug 30 '13 at 18:15

score 5 · Accepted Answer · answered Feb 19 '11 at 01:22

5

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

answered Feb 19 '11 at 01:22

Peter C

161
1
9

What would you expect the name the name of the output parameter to be? I've tried several alternatives – Conrad Frix Feb 22 '11 at 16:19
@Conrad Frix I believe you can assign it any name you want, by specifying the parameter direction as return value it would map the results, I haven't tested this yet. I also came across this link [return ref_cursor from function](http://stackoverflow.com/questions/4125992/how-to-return-a-refcursor-from-oracle-function/4126886#4126886). They use ExecuteNonQuery and access the cursor via the parameter. Is this the alternative way you found? – Peter C Feb 22 '11 at 18:16
that did it. Enjoy your revival badge – Conrad Frix Feb 22 '11 at 20:07
The alternative that I found was to use the procs directly and forget about writing wrapper functions. See the bottom of [this question](http://stackoverflow.com/q/3526798/119477) – Conrad Frix Feb 22 '11 at 20:18
@Conrad Frix Yay I have two badges now =). Thank you! – Peter C Feb 23 '11 at 18:35

score 3 · Answer 2 · answered Feb 11 '19 at 06:38

I had to go up and down between the question and answers to figure out the full code that works. So I am giving the full code here that worked for me for others -

var sql = @"BEGIN :refcursor1 := mypackage.myfunction(:param1) ; end;";
using(OracleConnection con = new OracleConnection("<connection string>"))
using(OracleCommand com = new OracleCommand())
{
     com.Connection = con;
     con.Open();
     com.Parameters.Add(":refcursor1", OracleDbType.RefCursor, ParameterDirection.Output);
     com.Parameters.Add(":param1", "param");
     com.CommandText = sql;
     com.CommandType = CommandType.Text;
     com.ExecuteNonQuery();
     OracleRefCursor curr = (OracleRefCursor)com.Parameters[0].Value;
     using(OracleDataReader dr = curr.GetDataReader())
     {
         if(dr.Read())
         {
             var value1 = dr.GetString(0);
             var value2 = dr.GetString(1);
         }
     }
 }

Hope it helps.

score 0 · Answer 3 · answered Apr 28 '13 at 01:23

I know this is quite an old post, but since it took me so long to figure out all of the minutia involved in getting .NET to "fight nice" with Oracle, I figured I'd put this advice out there for anyone else in this sticky situation.

I frequently call Oracle stored procedures that return a REF_CURSOR in our environment (.NET 3.5 against Oracle 11g). For a function, you can indeed name the parameter anything you'd like, but then you need to set its System.Data.ParameterDirection = ParameterDirection.ReturnValue then ExecuteNonQuery against the OracleCommand object. At that point the value of that parameter will be the ref_cursor that the Oracle function returned. Just cast the value as an OracleDataReader and loop through the OracleDataReader.

I'd post the full code, but I wrote the data access layer in VB.NET years ago, and the bulk of the code consuming the data access layer (our corporate intranet) is in C#. I figured mixing languages in a single response would be the larger faux pas.

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

3 Answers3

Linked