WebApi call stored-procedure passing UDT - Object reference not set to an instance of an object

[MatrixWPY]

Environment

• OS : Win10 (22H2)
• IDE : VS2022 (17.8.0)
• Project Type : ASP.NET Core Web API
• Target Framework : DotNet 6.0
• ORM : Dapper (2.1.28)
• DB Provider : Oracle.ManagedDataAccess.Core (3.21.130)
• DB : Oracle Database 21c Express Edition (21.3.0.0.0)

UDT in DB

create or replace TYPE UDT_LONG AS OBJECT (LongData NUMBER);
create or replace TYPE TB_UDT_LONG AS TABLE OF UDT_LONG;

Stored-Procedure

create or replace PROCEDURE SP_QUERYPRODUCTV5(
    i_categoryId NUMBER DEFAULT NULL,
    i_categoryIds TB_UDT_LONG DEFAULT NULL,
    i_productName VARCHAR2 DEFAULT NULL,
    i_rowStart NUMBER DEFAULT 0,
    i_rowLength NUMBER DEFAULT 10,
    o_result OUT SYS_REFCURSOR
)
AS 
BEGIN
    OPEN o_result FOR
    WITH CTE AS
    (
        SELECT
            P.PRODUCT_ID,
            P.PRODUCT_NAME,
            P.DESCRIPTION,
            P.STANDARD_COST,
            P.LIST_PRICE,
            PC.CATEGORY_ID,
            PC.CATEGORY_NAME
        FROM
            PRODUCTS P
        INNER JOIN
            PRODUCT_CATEGORIES PC
                ON P.CATEGORY_ID = PC.CATEGORY_ID
        WHERE 1 = 1
        AND
        (
            (i_categoryId IS NULL)
            OR
            (PC.CATEGORY_ID = i_categoryId)
        )
        AND
        (
            (i_categoryIds IS NULL)
            OR
            PC.CATEGORY_ID IN (SELECT * FROM TABLE(CAST(i_categoryIds AS TB_UDT_LONG)))
        )
        AND
        (
            (i_productName IS NULL)
            OR
            (LOWER(P.PRODUCT_NAME) LIKE i_productName)
        )
    )
    SELECT
        C.*,
        COALESCE(SUM(I.QUANTITY), 0) AS QUANTITY,
        COUNT(*) OVER() AS TotalCnt
    FROM
        CTE C
    LEFT JOIN
        INVENTORIES I
            ON C.PRODUCT_ID = I.PRODUCT_ID
    GROUP BY
        C.PRODUCT_ID,
        C.PRODUCT_NAME,
        C.DESCRIPTION,
        C.STANDARD_COST,
        C.LIST_PRICE,
        C.CATEGORY_ID,
        C.CATEGORY_NAME
    ORDER BY C.PRODUCT_ID DESC
    OFFSET i_rowStart ROWS FETCH NEXT i_rowLength ROWS ONLY;
END SP_QUERYPRODUCTV5;

UDT in Code

public class UdtLong : INullable, IOracleCustomType
{
    private bool _isNull;
    private long? _longData;

    public virtual bool IsNull
    {
        get
        {
            return _isNull;
        }
    }

    public static UdtLong Null
    {
        get
        {
            UdtLong udt = new UdtLong();
            udt._isNull = true;
            return udt;
        }
    }

    [OracleObjectMapping("LONGDATA")]
    public long? LongData
    {
        get
        {
            return _longData;
        }
        set
        {
            _longData = value;
        }
    }

    public virtual void FromCustomObject(OracleConnection con, object udt)
    {
        if (_longData != null)
        {
            OracleUdt.SetValue(con, udt, "LONGDATA", _longData);
        }
    }

    public virtual void ToCustomObject(OracleConnection con, object udt)
    {
        _longData = (long?)OracleUdt.GetValue(con, udt, "LONGDATA");
    }
}

[OracleCustomTypeMapping("UDT_LONG")]
public class UdtLongFactory : IOracleCustomTypeFactory
{
    // Implementation of IOracleCustomTypeFactory.CreateObject()
    public IOracleCustomType CreateObject()
    {
        // Return a new custom object
        return new UdtLong();
    }
}

public class TbUdtLong : INullable, IOracleCustomType
{
    private bool _isNull;
    private IEnumerable<UdtLong> _udtLongData;

    public virtual bool IsNull
    {
        get
        {
            return _isNull;
        }
    }

    public static TbUdtLong Null
    {
        get
        {
            TbUdtLong udt = new TbUdtLong();
            udt._isNull = true;
            return udt;
        }
    }

    [OracleObjectMapping("UDTLONGDATA")]
    public IEnumerable<UdtLong> UdtLongData
    {
        get
        {
            return _udtLongData;
        }
        set
        {
            _udtLongData = value;
        }
    }

    public void FromCustomObject(OracleConnection con, object udt)
    {
        if (_udtLongData != null)
        {
            OracleUdt.SetValue(con, udt, "UDTLONGDATA", _udtLongData);
        }
    }

    public void ToCustomObject(OracleConnection con, object udt)
    {
        _udtLongData = (IEnumerable<UdtLong>)OracleUdt.GetValue(con, udt, "UDTLONGDATA");
    }
}

[OracleCustomTypeMapping("TB_UDT_LONG")]
public class TbUdtLongFactory : IOracleCustomTypeFactory
{
    // Implementation of IOracleCustomTypeFactory.CreateObject()
    public IOracleCustomType CreateObject()
    {
        // Return a new custom object
        return new TbUdtLong();
    }
}

OracleDynamicParameters

    public class OracleDynamicParameters : Dapper.SqlMapper.IDynamicParameters
    {
        private static Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>();

        private Dictionary<string, ParamInfo> parameters = new Dictionary<string, ParamInfo>();
        private List<object> templates;

        private class ParamInfo
        {

            public string Name { get; set; }

            public object Value { get; set; }

            public ParameterDirection ParameterDirection { get; set; }

            public OracleDbType? DbType { get; set; }

            public int? Size { get; set; }

            public IDbDataParameter AttachedParam { get; set; }

            public string? UdtTypeName { get; set; }
        }

        /// <summary>
        /// construct a dynamic parameter bag
        /// </summary>
        public OracleDynamicParameters()
        {
        }

        /// <summary>
        /// construct a dynamic parameter bag
        /// </summary>
        /// <param name="template">can be an anonymous type or a DynamicParameters bag</param>
        public OracleDynamicParameters(object template)
        {
            AddDynamicParams(template);
        }

        /// <summary>
        /// Append a whole object full of params to the dynamic
        /// EG: AddDynamicParams(new {A = 1, B = 2}) // will add property A and B to the dynamic
        /// </summary>
        /// <param name="param"></param>
        public void AddDynamicParams(
#if CSHARP30
            object param
#else
            dynamic param
#endif
        )
        {
            var obj = param as object;
            if (obj != null)
            {
                var subDynamic = obj as OracleDynamicParameters;
                if (subDynamic == null)
                {
                    var dictionary = obj as IEnumerable<KeyValuePair<string, object>>;
                    if (dictionary == null)
                    {
                        templates = templates ?? new List<object>();
                        templates.Add(obj);
                    }
                    else
                    {
                        foreach (var kvp in dictionary)
                        {
#if CSHARP30
                            Add(kvp.Key, kvp.Value, null, null, null);
#else
                            Add(kvp.Key, kvp.Value);
#endif
                        }
                    }
                }
                else
                {
                    if (subDynamic.parameters != null)
                    {
                        foreach (var kvp in subDynamic.parameters)
                        {
                            parameters.Add(kvp.Key, kvp.Value);
                        }
                    }

                    if (subDynamic.templates != null)
                    {
                        templates = templates ?? new List<object>();
                        foreach (var t in subDynamic.templates)
                        {
                            templates.Add(t);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Add a parameter to this dynamic parameter list
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <param name="direction"></param>
        /// <param name="size"></param>
        /// <param name="udtTypeName"></param>
        public void Add(
#if CSHARP30
            string name, object value, DbType? dbType, ParameterDirection? direction, int? size, string? udtTypeName
#else
            string name, object value = null, OracleDbType? dbType = null, ParameterDirection? direction = null, int? size = null, string? udtTypeName = null
#endif
        )
        {
            parameters[Clean(name)] = new ParamInfo() { Name = name, Value = value, ParameterDirection = direction ?? ParameterDirection.Input, DbType = dbType, Size = size, UdtTypeName = udtTypeName };
        }

        private static string Clean(string name)
        {
            if (!string.IsNullOrEmpty(name))
            {
                switch (name[0])
                {
                    case '@':
                    case ':':
                    case '?':
                        return name.Substring(1);
                }
            }
            return name;
        }

        void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            AddParameters(command, identity);
        }

        /// <summary>
        /// Add all the parameters needed to the command just before it executes
        /// </summary>
        /// <param name="command">The raw command prior to execution</param>
        /// <param name="identity">Information about the query</param>
        protected void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            if (templates != null)
            {
                foreach (var template in templates)
                {
                    var newIdent = identity.ForDynamicParameters(template.GetType());
                    Action<IDbCommand, object> appender;

                    lock (paramReaderCache)
                    {
                        if (!paramReaderCache.TryGetValue(newIdent, out appender))
                        {
                            appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, false);
                            paramReaderCache[newIdent] = appender;
                        }
                    }

                    appender(command, template);
                }
            }

            foreach (var param in parameters.Values)
            {
                string name = Clean(param.Name);
                bool add = !((OracleCommand)command).Parameters.Contains(name);
                OracleParameter p;
                if (add)
                {
                    p = ((OracleCommand)command).CreateParameter();
                    p.ParameterName = name;
                }
                else
                {
                    p = ((OracleCommand)command).Parameters[name];
                }
                var val = param.Value;
                p.Value = val ?? DBNull.Value;
                p.Direction = param.ParameterDirection;
                var s = val as string;
                if (s != null)
                {
                    if (s.Length <= 4000)
                    {
                        p.Size = 4000;
                    }
                }
                if (param.Size != null)
                {
                    p.Size = param.Size.Value;
                }
                if (param.DbType != null)
                {
                    p.OracleDbType = param.DbType.Value;
                }
                if (param.UdtTypeName != null)
                {
                    p.UdtTypeName = param.UdtTypeName;
                }
                if (add)
                {
                    command.Parameters.Add(p);
                }
                param.AttachedParam = p;
            }
        }

        /// <summary>
        /// All the names of the param in the bag, use Get to yank them out
        /// </summary>
        public IEnumerable<string> ParameterNames
        {
            get
            {
                return parameters.Select(p => p.Key);
            }
        }

        /// <summary>
        /// Get the value of a parameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <returns>The value, note DBNull.Value is not returned, instead the value is returned as null</returns>
        public T Get<T>(string name)
        {
            var val = parameters[Clean(name)].AttachedParam.Value;
            if (val == DBNull.Value)
            {
                if (default(T) != null)
                {
                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type!");
                }
                return default(T);
            }
            return (T)val;
        }
    }

Dapper call Stored-Procedure

public (int, IEnumerable<QueryProductRP>) QueryProductV5(Dictionary<string, object> dicParams)
{
    try
    {
        var dynamicParam = new OracleDynamicParameters();
        dynamicParam.Add("i_categoryId", dicParams.GetValueOrDefault("CategoryId"), OracleDbType.Int64, ParameterDirection.Input);

        var aryUdtLong = ((IEnumerable<long>)dicParams.GetValueOrDefault("CategoryIds"))
                         ?.Select(e => new UdtLong() { LongData = e }).ToArray();
        dynamicParam.Add(
            "i_categoryIds",
            aryUdtLong == null ? null : new TbUdtLong { UdtLongData = aryUdtLong },
            OracleDbType.Object,
            ParameterDirection.Input,
            udtTypeName: "TB_UDT_LONG"
        );

        dynamicParam.Add("i_productName", dicParams.GetValueOrDefault("ProductName"), OracleDbType.Varchar2, ParameterDirection.Input);
        dynamicParam.Add("i_rowStart", dicParams.GetValueOrDefault("RowStart"), OracleDbType.Int32, ParameterDirection.Input);
        dynamicParam.Add("i_rowLength", dicParams.GetValueOrDefault("RowLength"), OracleDbType.Int32, ParameterDirection.Input);
        dynamicParam.Add("o_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

        var res = _dbConnection.Query<QueryProductV5RP>(
            "SP_QUERYPRODUCTV5",
            dynamicParam,
            commandType: CommandType.StoredProcedure
        );

        return (res.FirstOrDefault()?.TotalCnt ?? 0, res);
    }
    catch
    {
        throw;
    }
}

Exception

Object reference not set to an instance of an object.
   at Oracle.ManagedDataAccess.Types.OracleUdt.GetAllReferencedAssemblies()
   in Oracle.ManagedDataAccess.Types\OracleUdt.cs:line 741

[alexkeh]

Can you turn on ODP.NET tracing and upload the trace file? You can do so by adding the following two lines ahead of opening your first connection.

OracleConfiguration.TraceFileLocation = @"C:\traces";
OracleConfiguration.TraceLevel = 7;

It will be easier to initially triage the issue based on that. If this app worked before, what changes occurred between the last time it worked and now?

[MatrixWPY]

Thanks for response :) Now I have added the configure to record trace file, but it comes out another similar exception.

OracleConfiguration.TraceFileLocation = @"D:\traces";
OracleConfiguration.TraceLevel = 7;

var res = _dbConnection.Query<QueryProductV5RP>(
    "SP_QUERYPRODUCTV5",
    dynamicParam,
    commandType: CommandType.StoredProcedure
);

Object reference not set to an instance of an object.
   at Oracle.ManagedDataAccess.Types.OracleUdt.GetAllReferencedAssemblies()
   in Oracle.ManagedDataAccess.Types\OracleUdt.cs:line 739

It seems like "Assembly.Load()" from "Anonymously Hosted DynamicMethods Assembly" would cause "assembly2" = null. The strangest thing is even this exception happened, my code still works fine (query condition with UDT parameter). And It only happen on the first query (UDT parameter), the following query wouldn't cause this exception again.

Upload the trace file, thanks :) TraceFile.zip

[MatrixWPY]

@alexkeh any suggestion? I would like to know is there any configuration missing or it is a bug for current version? Hope to get any feedback soon :)

[alexkeh]

@MatrixWPY The trace doesn't provide enough information to identify the root cause unfortunately. Something isn't being loaded, but it's not clear what it is with the current trace info. Does this exception message occur at runtime without a debugger?

The next step would be getting you a diagnostic drop with enhanced tracing to see how the object reference problem arises. Open an Oracle Support service request and request the support analyst to contact me within Oracle. I'll work with support to provide you an enhanced trace to run.

[MatrixWPY]

Thanks for response :) Recently I had deployed my project on IIS, and everything works fine (no error log). Looks like the exception only happened on debug mode.