Hello!
I've faced with strange behaviour while calling procedure over ODP.NET with nested cursors inside.
1. I've created simple Oracle function under example 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, created 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. Obtained:
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
It seems that there is no support for nested cursors.
How can I parse cursor with nested cursor returned from procedure over ODP.NET?
Note: no problems with JDBC.
Thanks in advance!
You're right - there is no support for nested cursors in ODP.NET.
You'll have to take a different approach. e.g. returning the rows as XML or JSON instead of in a nested cursor.