ODP .NET Getting a SYSREFCURSOR output. {"Unsupported column datatype"} error

Question

I am trying to execute a procedure that returns a stored procedure. My version of Oracle DB is 9.2 and the ODP .NET version is 10.2.0.100

My C# code looks like this.

OracleCommand od = new OracleCommand();
od.Connection = oc;
OracleParameter opBranchNo;
OracleParameter opSysRef;
od.CommandType = System.Data.CommandType.StoredProcedure;
od.CommandText = "pkg_fetchleaseinfo.proc_fetchleaseheader";

opBranchNo = new OracleParameter("IBRANCH_ID", OracleDbType.Varchar2, 3, "044");
opBranchNo.Direction = System.Data.ParameterDirection.Input;
od.Parameters.Add(opBranchNo);

opSysRef = new OracleParameter();
opSysRef.ParameterName = "REC_SET";
opSysRef.Direction = System.Data.ParameterDirection.Output;
opSysRef.OracleDbType = OracleDbType.RefCursor;
od.Parameters.Add(opSysRef);
od.Prepare();
od.ExecuteNonQuery();
Oracle.DataAccess.Types.OracleRefCursor sysref = 
    (Oracle.DataAccess.Types.OracleRefCursor)opSysRef.Value;
return sysref.GetDataReader();
//OracleDataReader dr1 = 
//((Oracle.DataAccess.Types.OracleRefCursor)opSysRef.Value).GetDataReader();
//return dr1;

My Oracle Procedure code looks like this

PROCEDURE proc_fetchleaseheader(ibranch_id IN VARCHAR2,
    rec_set OUT SYS_REFCURSOR) IS x_rec genericCursor; 
BEGIN
   OPEN x_rec FOR SELECT getleaseheaderrows(ibranch_id) FROM dual;
   rec_set := x_rec;  
EXCEPTION WHEN OTHERS THEN     
   RAISE; 
END;

When I execute my code, the part where I attempt a GetReader() fails with an UNSUPPORTED COLUMN DATATYPE error message.

For some reason, I have not been able to format the stored procedure code correctly. — abhi
– abhi, Commented Aug 10, 2010 at 12:22
getleaseheaderrows(ibranch_id) is a user-defined function. What datatype does it return? Does your ODP stuff work if the stored procedure calls something simpler, like select dummy from dual? — APC
– APC, Commented Aug 10, 2010 at 13:20
getleaseheaderrows(ibranch_id) is a function that returns a REF CURSOR. If I try with SELECT DUMMY FROM DUAL; the function throws an error that looks like this. Error System.NullReferenceException: Object reference not set to an instance of an object. — abhi
– abhi, Commented Aug 10, 2010 at 13:59
I have been able to fix that NullReferenceException. I was executing ExecuteReader(). I changed that code to ExecuteNonQuery(). I am able to successfully retrieve the value from SYSREFCURSOR with a SELECT DUMMY FROM DUAL; — abhi
– abhi, Commented Aug 10, 2010 at 14:23

Harrison · Accepted Answer · 2010-08-10 14:33:09Z

1

I believe you are opening a refCursor to hold a Select [RefCursor] from dual

why don't you just

PROCEDURE proc_fetchleaseheader(ibranch_id IN VARCHAR2,
    rec_set OUT SYS_REFCURSOR) IS x_rec genericCursor; 
BEGIN
   x_rec := getleaseheaderrows(ibranch_id);
   rec_set := x_rec;  
/**EXCEPTION WHEN OTHERS THEN     --no need for this, the proc will raise just fine without being explicitly told to do so
   RAISE; 
***/
END;

or better yet just call getleaseheaderrows from the .net side and drop the procedure (just remember for parameters in ODP it always expects the function return value as the first param.

answered Aug 10, 2010 at 14:33

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abhi Over a year ago

Do you know if things will change when we move to a PIPELINED function?

Collectives™ on Stack Overflow

ODP .NET Getting a SYSREFCURSOR output. {"Unsupported column datatype"} error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related