Oracle ODP.NET - System.Exception: 调用嵌套游标时，“unmarshalcolumndata: 未实现的类型”

你好!
我在ODP.NET上使用嵌套游标调用过程时遇到了奇怪的行为。

1.我已经在示例模式下创建了简单的Oracle函数:

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.然后，创建了简单的C # 控制台应用程序:

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.运行它。

4.获得:

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

似乎没有支持嵌套游标。
如何通过ODP.NET从过程中返回的嵌套游标解析游标？
注意: JDBC没有问题。

提前谢谢!

你是对的-ODP.NET中不支持嵌套游标。

您将不得不采取不同的方法。例如，将行返回为XML或JSON，而不是在嵌套游标中。