C# Pass array of objects (UDT) to oracle stored procedure returns exception 'Column contains NULL data' on ExecuteReader

Question

I'm trying to create a test app that will pass an array of objects (in my case Employees) to an Oracle stored procedure.

When I call the ExecuteReader to call the procedure, I get an error

Column contains NULL data

but I have no idea what that means.

Here is the code I use. I have the following class:

[OracleCustomTypeMapping("EMTEST")]
public class Employee : IOracleCustomType, INullable
{

    public Employee(int? Id, string Name)
    {
        this.Id = Id;
        this.Name = Name;
    }

    [OracleObjectMapping("Name")]
    public string Name { get; set; }

    [OracleObjectMapping("Id")]
    public int? Id { get; set; }

    public bool IsNull { get; set; }

    public static Employee Null => new Employee(0, "") { IsNull = true };

    public void FromCustomObject(OracleConnection con, object udt)
    {
        OracleUdt.SetValue(con, udt, "Id", Id);
        OracleUdt.SetValue(con, udt, "Name", Name);
    }

    public void ToCustomObject(OracleConnection con, object udt)
    {
        Id = ((int?)(OracleUdt.GetValue(con, udt, "Id")));
        Name = ((string)(OracleUdt.GetValue(con, udt, "Name")));
    }
}

On oracle side i create a type:

create or replace TYPE EMTEST AS OBJECT
(
     Id number  null,
     Name varchar2(50)  null    
);

And a procedure in a package called PKS_CARAT

This is the header (where the table of the type is defined) :

type T_EMTEST_ARRAY is table of EMTEST;

procedure TestGetObjectsBack(
        Employes in T_EMTEST_ARRAY,
        ResultTest OUT RC_Result
);

This is the body:

procedure TestGetObjectsBack(
        Employes in T_EMTEST_ARRAY,
        ResultTest OUT RC_Result
)
is
    
begin

OPEN ResultTest FOR
select 1 as Id,'mika' as Name from dual;

-- select e.Id,e.Name from table (Employes) e;
    
end TestGetObjectsBack;

This is the code that calls the stored procedure:

var oracleConnection = new OracleConnection("User Id=xxx;Password=xxx;Data Source=ORAKISDEV2;Connection Lifetime=10;Min Pool Size=1;Max Pool Size=200;");

var employeList = new List<Employee>()
{
    new Employee(1,"Zoki"),
    new Employee(2, "Pera"),
};

var employeArray = employeList.ToArray();

var employeParam = new OracleParameter("Employes", OracleDbType.Object,  ParameterDirection.Input);
employeParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

employeParam.Size = employeArray.Length;
employeParam.UdtTypeName = "PKS_CARAT.T_EMTEST_ARRAY";
employeParam.Value = employeArray;

var cursor = new OracleParameter("ResultTest", OracleDbType.RefCursor, ParameterDirection.Output);

oracleConnection.Open();
using (var oracleCommand = oracleConnection.CreateCommand())
{
    oracleCommand.Connection = oracleConnection;
    oracleCommand.CommandText = "PKS_CARAT.TestGetObjectsBack";
    oracleCommand.CommandType = CommandType.StoredProcedure;
    oracleCommand.BindByName = true;

    oracleCommand.Parameters.Add(employeParam);
    oracleCommand.Parameters.Add(cursor);


var reader = oracleCommand.ExecuteReader();
....

I read many articles, and the most important are:

1. pass array of objects to oracle stored procedure using ODP.Net managed client
2. Why do I get "Invalid Parameter binding" when using Oracle stored procedure with Udt and C#

I tried many sugesstions but none of them made any difference. I still get the Column contains NULL data without any explanation which data is null or why this issue is caused. I checked if my columns from the class and the oracle type are the same and it look slike they are.

Full exception text is: 'Column contains NULL data'. From 'Oracle.ManagedDataAccess'

Type of the exception is System.InvalidCastException

Im using nuget package Oracle.ManagedDataAccess.Core version 3.21.80

Here is the top of the stack trace of the exception:

OracleInternal.UDT.Types.UDTNamedType.GetNamedTypeMetaData(OracleConnection conn, OracleCommand getTypeCmd)
   at OracleInternal.UDT.Types.UDTTypeCache.GetMetaData(OracleConnection conn, UDTNamedType udtType)
   at OracleInternal.UDT.Types.UDTTypeCache.CreateUDTType(OracleConnection conn, String schemaName, String typeName)
   at OracleInternal.UDT.Types.UDTTypeCache.GetUDTType(OracleConnection conn, String schemaName, String typeName)
   at OracleInternal.ConnectionPool.OraclePoolManager.GetUDTType(OracleConnection conn, String schemaName, String typeName)
   at Oracle.ManagedDataAccess.Client.OracleConnection.GetUDTTypeFromCache(String schemaName, String typeName)
   at Oracle.ManagedDataAccess.Client.OracleParameter.GetUDTType(OracleConnection conn)
   at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind_UDT(OracleConnection conn)
   at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind(OracleConnectionImpl connImpl, ColumnDescribeInfo cachedParamMetadata, Boolean& bMetadataModified, Int32 arrayBindCount, ColumnDescribeInfo& paramMetaData, Object& paramValue, Boolean isEFSelectStatement, SqlStatementType stmtType)
   at OracleInternal.ServiceObjects.OracleCommandImpl.InitializeParamInfo(ICollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ProcessParameters(OracleParameterCollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bBindMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
   at OracleArrayTest.Program.TestCollectionCustom() in C:\project\BulkProject\OracleArrayTest\Program.cs:line 203

Does anybody know what am i doing wrong? What is this exception telling me?

Answer 1

I found this article:

• https://github.com/oracle/dotnet-db-samples/issues/56

And somebody in the comments gives a link to examples on how to send arrays of custom objects to oracle(best example for me was Nested-Table.cs):

• https://github.com/oracle/dotnet-db-samples/tree/master/samples/udt

After reading the comments and looking at the example i changed the following:

• I defined the T_EMTEST_ARRAY also in the Types, where i have defined EMTEST ( the body looks like this CREATE OR REPLACE TYPE "T_EMTEST_ARRAY" AS TABLE OF EMTEST;

• I downloaded and installed the extension Oracle Dev tools for Visual Studio ( best if you install it with c++ module from visual studio installer)

• I used the extension to generate classes from oracle for T_EMTEST_ARRAY and EMTEST ( you connect to the db with the tool and then can generate c# code from types)

• I removed the T_EMTEST_ARRAY defined in the package PKS_CARAT because its now define on a global level

• I deleted the Employee that i created by myself

• I used the generated classes to create an array. Here is the c# code:

           var emp1 = new EMTEST();
  
           emp1.IME = "Zika";
           emp1.JMBG = 1;
  
           var emp2 = new EMTEST();
  
           emp2.JMBG = 2;
           emp2.IME = "Mika";
  
           EMTEST[] emtestArray = new EMTEST[] { emp1, emp2 };
  
           OracleParameter param = new OracleParameter();
           param.OracleDbType = OracleDbType.Array;
           param.Direction = ParameterDirection.Input;
  
           // Note: The UdtTypeName is case-senstive
           param.UdtTypeName = "T_EMTEST_ARRAY";
           param.Value = emtestArray;
  
           var cursor = new OracleParameter("ResultTest", OracleDbType.RefCursor, ParameterDirection.Output);
  
  
           oracleConnection.Open();
           using (var oracleCommand = oracleConnection.CreateCommand())
           {
               oracleCommand.Connection = oracleConnection;
               oracleCommand.CommandText = "PKS_CARAT.TestGetObjectsBack";
               oracleCommand.CommandType = CommandType.StoredProcedure;
  
               oracleCommand.Parameters.Add(param);
  
                oracleCommand.Parameters.Add(cursor);
  
  
               var reader = oracleCommand.ExecuteReader();
  
               while (reader.Read())
               {
                   var jmbg = reader[0];
                   var ime = reader[1];
               }
               reader.Close();
           }
  

after that it worked. The hard part was to find the right thread. You can read more about oracle introducing the UDTs for .net core here:

• https://medium.com/oracledevs/announcing-odp-net-21-3-user-defined-types-for-managed-odp-net-and-odp-net-core-836fa81da3ba