Skip to Main Content
  • Questions
  • ODP.NET - System.Exception: 'UnmarshalColumnData: Unimplemented type' when calling nested cursor

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Hasan.

Asked: March 02, 2018 - 1:44 pm UTC

Last updated: March 08, 2018 - 1:35 pm UTC

Version: 12.1.1

Viewed 1000+ times

You Asked

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!

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hasan, March 09, 2018 - 8:02 am UTC

Thank you very much!

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here