C# - ODP.NET - System.Exception: 'UnmarshalColumnData: Unimplemented type' when calling nested cursor

0

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.

c#
oracle
cursor
odp.net
asked on Stack Overflow Feb 23, 2018 by Hasan Alizada

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0