1) Let's create simple Oracle function under your schema:
CREATE OR REPLACE FUNCTION TEST_CUR
RETURN SYS_REFCURSOR
IS
OUT_CUR SYS_REFCURSOR;
BEGIN
OPEN OUT_CUR FOR
SELECT '1', '2', '3', CURSOR (SELECT '4' FROM DUAL) FROM DUAL;
RETURN OUT_CUR;
END;
2) Then, create simple C# console application:
using System;
using Oracle.ManagedDataAccess.Client;
using System.Data;
class Sample
{
static void Main()
{
string constr = "User Id=MY_USER;Password=MY_USER_PASSWORD;Data Source=MY_DB";
OracleConnection conn = new OracleConnection(constr);
conn.Open();
OracleCommand myCMD = new OracleCommand();
myCMD.Connection = conn;
myCMD.CommandText = "test_cur";
myCMD.CommandType = CommandType.StoredProcedure;
myCMD.Parameters.Add(new OracleParameter("p_cursor", OracleDbType.RefCursor)).Direction = ParameterDirection.ReturnValue;
OracleDataReader myReader = default(OracleDataReader);
try
{
myCMD.ExecuteNonQuery();
}
catch (Exception myex)
{
Console.WriteLine(" " + myex.Message);
}
myReader = myCMD.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine(myReader.GetString(0) + myReader.GetString(1) + myReader.GetString(2) + myReader.GetDataTypeName(1));
}
myReader.Close();
conn.Close();
conn.Dispose();
Console.ReadLine();
}
}
3) Run it.
4) Obtain:
System.Exception: 'UnmarshalColumnData: Unimplemented type'
System.Exception occurred
HResult=0x80131500
Message=UnmarshalColumnData: Unimplemented type
Source=Oracle.ManagedDataAccess
StackTrace:
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)
at OracleInternal.ServiceObjects.OracleDataReaderImpl.FetchMoreRows(Int32 noOfRowsToFetch, Boolean fillReader, Boolean returnPSTypes)
at Oracle.ManagedDataAccess.Client.OracleDataReader.Read()
at Sample.Main() in Program.cs:line 39
5) It seems that there is no support for nested cursors.
6) Suggestions and workarounds?
P.S.
Please, do not suggest to change Oracle function, change to procedure with OUT cursor parameters etc.
Only this function. Only this console application.
User contributions licensed under CC BY-SA 3.0