Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to call a function that returns a SYS_REFCURSOR?

abhijit74Feb 15 2011 — edited Feb 15 2011
Here's the situation.
If I use ODP. NET to fetch a REF_CURSOR from a procedure in a package it works without a hitch.

If I use ODP .NET to execute an Oracle function that returns a REF CURSOR, all sorts of things go wrong.
I am using .NET 3.5 and ODP .NET version 2.112.1.2 (11 G). My Oracle Database is 9.2.0.7.0

There is this function that returns a REF CURSOR.
The code looks like something like this.
CREATE OR REPLACE FUNCTION SENDMEAREFCURSOR RETURN SYS_REFCURSOR IS
  X_REF genPkg.genericcursor;
BEGIN
  OPEN X_REF for
    SELECT customer_id, name FROM CUSTOMERS;
  Return X_REF;
END;
The .NET code looks like this. This is after some tweaking.
        private static OracleDataReader FireMyFunc(OracleConnection oc)
        {
            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = oc;
                //cmd.CommandText = "SELECT sendmearefcursor FROM DUAL";
                //cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "sendmearefcursor";
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter prm = cmd.CreateParameter();
                prm.OracleDbType = OracleDbType.RefCursor;
                prm.ParameterName = "returncurse";
                prm.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(prm);
                cmd.ExecuteNonQuery();
                return (OracleDataReader) prm.Value; //Throws an UnableToCast exception.
            }
            catch (System.Exception ex)
            {
               Console.WriteLine(String.Format("FireMyFunc: {0}",ex.ToString()));
                throw;
            }
        }
What do I need to do in order to get the refCursor?
This post has been answered by JennyJ-Oracle on Feb 15 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 15 2011
Added on Feb 15 2011
1 comment
850 views