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?