Calling Oracle's Stored Procedures and Mapping User Defined Types (UDTs) from ASP.NET

Introduction

Calling Oracle's stored procedures which use UDTs as input and output parameters can be a daunting task at first. There are few samples and little documentation available and there are some pitfalls which can be easy to fall into and difficult to diagnose and understand. My goal is to help you fellow developers to avoid hitting your head against the wall for 3 days like I did.

Background

Calling stored procedures itself from a .NET program is a no brainer for the intermediate skilled developer, but calling a procedure that uses UDTs as input and output types is a bit more difficult task specifically from ASP.NET MVC / Rest API due to certain aspects of these project types.

Oracle provides some documentation on how to achieve this, and there are good articles on how to do this here on CodeProject.

However, neither of these articles mention anything about a common mistake I'd like to help you avoid.

The Common Problems

The first problem arises from the fact that Oracles' managed drivers do not support UDTs. So you cannot use the ones you get from Nuget. You need to use non-managed ones which you can get by downloading and installing Oracle developer tools for Visual Studio 2017 (from here). The data providers can be found at the installation directory in %installation directory%\Oracle_12_1-32\odp.net\bin\.

The second problem arises from using the correct version of the data provider. There are 2 data providers: 2.x for older .NET Framework and 4 for 4.0 .NET Framework onwards. Be sure to use the correct one. I had to remove the 2.x version from GAC to get the app to run with 4.0 .NET version. You can do this by opening the Developer command prompt in Administrative- mode and running the following commands:

To see which version of the DLLs are in GAC, run the following:

gacutil /l Oracle.DataAccess

To remove a specific version, run the following command:

gacutil /u Oracle.DataAccess Version=x.y.z.i, PublicKeyToken=abcdefghijklmnopqrstuvwxyz

Obviously, replace version and public key token values to the ones you want to remove. More information on gacutil can be found here.

The last and the biggest problem (at least for me) comes from the way Oracle uses reflection and the way Microsoft uses System.Reflection.Emit to generate dynamically created code to IL). This causes a difficult to debug- problem when mapping the UDTs: System.IO.FileNotFoundException: 'Could not load file or assembly 'Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'

Mapping UDT's to C# Classes

Oracle uses reflection to map UDT types to classes. This is why Oracle requires you to add attributes to classes and properties that act as UDT types and also to implement a couple of interfaces. These interfaces and attributes allow Oracle's data provider to find and map UDTs to your classes.

There are 2 interfaces which you need to implement and 2 attributes you must use. The interfaces are called IOracleCustomType and IOracleCustomTypeFactory interfaces. The attributes are called OracleCustomTypeMapping and OracleObjectMapping.

I believe in learning through samples to let's get down to it.

Defining UDTs

A Sample UDT is defined in Oracles' PL/SQL - language like this:

create or replace TYPE GENERIC_REPLY_OUT AS OBJECT (
   REPLY_HDR               REPLY_HDR_DEF,                
   GRIDCOLS                     GRIDCOLS_TBL,
   GRIDROWS                    GRIDROWS_TBL
) NOT FINAL;

Where REPLY_HDR_DEF is defined as:

create or replace TYPE REPLY_HDR_DEF AS OBJECT (
    START_TS   VARCHAR2(22),      
    END_TS     VARCHAR2(22),
    RUN_STATUS VARCHAR(3),     
    ERROR_TEXT VARCHAR2 (200),   
    USER_ID    VARCHAR2(20),
    RUN_ID     VARCHAR2(32)
) NOT FINAL;

and GRIDCOLS_TBL and GRIDROWS_TBL like:

create or replace TYPE GRIDCOLS_TBL IS TABLE OF GRIDCOLS_DEF

create or replace TYPE GRIDROWS_TBL IS TABLE OF GRIDROWS_DEF

and GRIDCOLS_DEF and GRIDROWS_DEF:

create or replace TYPE GRIDCOLS_DEF AS OBJECT (
    COL_NAME   VARCHAR2(30),
    COL_TYPE   VARCHAR(20)
) NOT FINAL

create or replace TYPE GRIDROWS_DEF AS OBJECT (
    COL_VALS COL_VALS_TBL,
    ROW_STATUS VARCHAR2(10)
) NOT FINAL

And finally, the COL_VALS_TBL and COL_VALS_DEF:

create or replace TYPE COL_VALS_TBL IS TABLE OF COL_VALS_DEF

create or replace TYPE COL_VALS_DEF AS OBJECT (
    COL_VAL VARCHAR2(200)
) NOT FINAL

Matching C# Classes

First of all, to avoid repetitiveness, I added an abstract class that implements the necessary interfaces:

public abstract class CustomTypeBase<T> : 
    IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new()
    {
        private bool _isNull;
        public bool IsNull
        {
            get { return this._isNull; }
        }

        public static T Null
        {
            get { return new T { _isNull = true }; }
        }

        public IOracleCustomType CreateObject()
        {
            return new T();
        }

        public abstract void FromCustomObject(OracleConnection con, IntPtr pUdt);
        public abstract void ToCustomObject(OracleConnection con, IntPtr pUdt);        
    }

And here is how I defined the abstract class for the table- types:

public abstract class CustomCollectionTypeBase<TType, TValue> : CustomTypeBase<TType>, 
                   IOracleArrayTypeFactory where TType : CustomTypeBase<TType>, new()
    {
        [OracleArrayMapping()]
        public TValue[] Values;

        public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
        {
            OracleUdt.SetValue(connection, pointerUdt, 0, Values);
        }

        public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
        {
            Values = (TValue[])OracleUdt.GetValue(connection, pointerUdt, 0);
        }

        public Array CreateArray(int elementCount)
        {
            return new TValue[elementCount];
        }

        public Array CreateStatusArray(int elementCount)
        {
            return new OracleUdtStatus[elementCount];
        }
    }

Now the Generic reply class that maps to GENERIC_REPLY_OUT:

[OracleCustomTypeMapping("TEST.GENERIC_REPLY_OUT")]
    public class GenericReply : CustomTypeBase<GenericReply>
    {
        public GenericReply()
        {
            ReplyHeader = new ReplyHeaderDefinition();
            GridCols = new GridColsTable();
            GridRows = new GridRowsTable();
        }

        [OracleObjectMapping("REPLY_HDR")]
        public ReplyHeaderDefinition ReplyHeader { get; set; }
        [OracleObjectMapping("GRIDCOLS")]
        public GridColsTable GridCols { get; set; }
        [OracleObjectMapping("GRIDROWS")]
        public GridRowsTable GridRows { get; set; }

        public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "REPLY_HDR", ReplyHeader);
            OracleUdt.SetValue(con, pUdt, "GRIDCOLS", GridCols);
            OracleUdt.SetValue(con, pUdt, "GRIDROWS", GridRows);
        }

        public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            ReplyHeader = (ReplyHeaderDefinition)(OracleUdt.GetValue(con, pUdt, "REPLY_HDR"));
            GridCols = (GridColsTable)(OracleUdt.GetValue(con, pUdt, "GRIDCOLS"));
            GridRows = (GridRowsTable)(OracleUdt.GetValue(con, pUdt, "GRIDROWS"));
        }
    }

And the Reply header definition class that maps to REPLY_HDR_DEF:

[OracleCustomTypeMapping("TEST.REPLY_HDR_DEF")]
    public class ReplyHeaderDefinition : CustomTypeBase<ReplyHeaderDefinition>
    {
        public ReplyHeaderDefinition()
        {
            StartTimestamp = "";
            EndTimestamp = "";
            RunStatus = "";
            ErrorText = "";
            UserId = "";
            RunId = "";
        }

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

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

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

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

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

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

        public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "START_TS", StartTimestamp);
            OracleUdt.SetValue(con, pUdt, "END_TS", EndTimestamp);
            OracleUdt.SetValue(con, pUdt, "RUN_STATUS", RunStatus);
            OracleUdt.SetValue(con, pUdt, "ERROR_TEXT", ErrorText);
            OracleUdt.SetValue(con, pUdt, "USER_ID", UserId);
            OracleUdt.SetValue(con, pUdt, "RUN_ID", RunId);
        }

        public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            StartTimestamp = (string)(OracleUdt.GetValue(con, pUdt, "START_TS"));
            EndTimestamp = (string)(OracleUdt.GetValue(con, pUdt, "END_TS"));
            RunStatus = (string)(OracleUdt.GetValue(con, pUdt, "RUN_STATUS"));
            ErrorText = (string)(OracleUdt.GetValue(con, pUdt, "ERROR_TEXT"));
            UserId = (string)(OracleUdt.GetValue(con, pUdt, "USER_ID"));
            RunId = (string)(OracleUdt.GetValue(con, pUdt, "RUN_ID"));
        }
    }

Now it's time for the classes that map to GRIDCOLS_TBL and GRIDROWS_TBL:

[OracleCustomTypeMapping("TEST.GRIDCOLS_TBL")]
public class GridColsTable : CustomCollectionTypeBase<GridColsTable, GridColsDefinition>
{
}

[OracleCustomTypeMapping("TEST.GRIDROWS_TBL")]
public class GridRowsTable : CustomCollectionTypeBase<GridRowsTable, GridRowsDefinition>
{
}

GRIDCOLS_DEF:

[OracleCustomTypeMapping("TEST.GRIDCOLS_DEF")]
    public class GridColsDefinition : CustomTypeBase<GridColsDefinition>
    {
        [OracleObjectMapping("COL_NAME")]
        public string Name { get; set; }

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

        
        public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "COL_NAME", Name);
            OracleUdt.SetValue(con, pUdt, "COL_TYPE", Type);
        }

        public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            Type = ((string)(OracleUdt.GetValue(con, pUdt, "COL_TYPE")));
            Name = ((string)(OracleUdt.GetValue(con, pUdt, "COL_NAME")));
        }        
  }

GRIDROWS_DEF:

[OracleCustomTypeMapping("TEST.GRIDROWS_DEF")]
    public class GridRowsDefinition : CustomTypeBase<GridRowsDefinition>
    {
        public GridRowsDefinition()
        {
            ColumnValuesTable = new ColumnValuesTable();
        }

        [OracleObjectMapping("COL_VALS")]
        public ColumnValuesTable ColumnValuesTable { get; set; }

        public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "COL_VALS", ColumnValuesTable);
        }

        public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            ColumnValuesTable = ((ColumnValuesTable)(OracleUdt.GetValue(con, pUdt, "COL_VALS")));
        }
    }

And finally, the column values table COLVALS_TBL and the column values definition COLVALS_DEF:

[OracleCustomTypeMapping("TEST.COL_VALS_TBL")]
public class ColumnValuesTable : CustomCollectionTypeBase<ColumnValuesTable, ColumnValueDefinition>
{
}

public class ColumnValueDefinition : CustomTypeBase<ColumnValueDefinition>
    {
        public ColumnValueDefinition()
        {
            Value = "";
        }

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

        public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, "COL_VAL", Value);
        }

        public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            Value = ((string)(OracleUdt.GetValue(con, pUdt, "COL_VAL")));
        }
    }

And finally, this is how we call the stored procedure. Let's assume the procedure name is Test and it is defined as:

PROCEDURE Test3 (REP_o OUT GENERIC_REPLY_OUT);  

so in a controller or data access layer, we could write a function like:

private Object RunOracleProcedure(string procedureFullName, Object outputObject, string outputUDTType)
        {
            OracleTransaction transaction = null;
            OracleCommand cmd = null;

            //Start transaction
            transaction = Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            //Create command
            cmd = new OracleCommand();
            cmd.Connection = Connection;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = procedureFullName;

            //Create output object//Create output Parameter
            OracleParameter parameter_out = cmd.CreateParameter();
            parameter_out.OracleDbType = OracleDbType.Object;
            parameter_out.Direction = ParameterDirection.Output;
            parameter_out.ParameterName = "REP_o";
            parameter_out.UdtTypeName = outputUDTType;
            parameter_out.OracleDbTypeEx = OracleDbType.Object;
            cmd.Parameters.Add(parameter_out);

            //Run command and print result.
            cmd.ExecuteNonQuery();

            outputObject = parameter_out.Value;
            transaction.Commit();

            return outputObject;
        }

And call it like this:

private GenericReply RunTestProcedure ()
        {
            string<code> </code>procedureFullName;
            GenericReply outputObject;

            procedureFullName = "TEST.Test3";

            outputObject = (GenericReply ) RunOracleProcedure
                           (procedureFullName, outputObject, "TEST.GENERIC_REPLY_OUT");

            return outputObject;
        }

Pheww, that was a bit of work, but now it should be working! Right? In fact, in a Console app or in a WCF- service, it would work, but not in an ASP.NET MVC or ASP.NET REST API backend project.

The Problem with ASP.NET MVC / REST API Projects

The Oracle.DataAccess.dll uses reflection and goes through every class in every assembly to see which classes have the necessary mapping attributes.

This causes a problem since Microsoft ASP.NET project emits code to IL dynamically, and in doing so, it creates a dynamic (or virtual?) assembly which does not exist on disc. It is a placeholder for the dynamically added IL instructions. Now when Oracle tries to load this virtual assembly, it fails to do so as it does not exist in disk and the whole call fails miserably.

You can try it out. You get the following exception:

System.IO.FileNotFoundException: 'Could not load file or assembly 'Anonymously Hosted DynamicMethods 
Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. 
The system cannot find the file specified.'

And if you look the stack trace, you can see that the call originates from Oracle.DataAccess.dll:

mscorlib.dll!System.Reflection.RuntimeAssembly.nLoad(System.Reflection.AssemblyName fileName, 
string codeBase, System.Security.Policy.Evidence assemblySecurity, 
System.Reflection.RuntimeAssembly locationHint, ref System.Threading.StackCrawlMark stackMark, 
System.IntPtr pPrivHostBinder, bool throwOnFileNotFound, bool forIntrospection, 
bool suppressSecurityChecks)    Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoadAssemblyName
(System.Reflection.AssemblyName assemblyRef, System.Security.Policy.Evidence assemblySecurity, 
System.Reflection.RuntimeAssembly reqAssembly, ref System.Threading.StackCrawlMark stackMark, 
System.IntPtr pPrivHostBinder, bool throwOnFileNotFound, bool forIntrospection, 
bool suppressSecurityChecks)    Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoad
(string assemblyString, System.Security.Policy.Evidence assemblySecurity, 
ref System.Threading.StackCrawlMark stackMark, System.IntPtr pPrivHostBinder, 
bool forIntrospection)    Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoad
(string assemblyString, System.Security.Policy.Evidence assemblySecurity, 
ref System.Threading.StackCrawlMark stackMark, bool forIntrospection)    Unknown
mscorlib.dll!System.Reflection.Assembly.Load(string assemblyString)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.GetAllReferencedAssemblies()    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.SetCustomTypeMappings()    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.GetUdtName
(string customTypeName, string dataSource)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.SetUDTFromCustomObject
(Oracle.DataAccess.Client.OracleConnection conn, Oracle.DataAccess.Types.IOracleCustomType customObj, 
int i)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind_OracleObject
(Oracle.DataAccess.Client.OracleConnection conn)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind_Object
(Oracle.DataAccess.Client.OracleConnection conn)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind
(Oracle.DataAccess.Client.OracleConnection conn, System.IntPtr errCtx, int arraySize, 
bool bIsFromEF, bool bIsSelectStmt)    Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()    Unknown

So how do we prevent this from happening. Obviously, we need to find alternative ways to map the UDTs to classes, or we need to prevent the DynamicAssembly from emerging in the assemblies- list.

Luckily, Oracle does provide alternative method with XML for mapping classes to UDT types, but does not document this in any way which I found disturbing.

The latter solution would probably mean breaking the ASP.NET app somehow, so I did not even consider it.

Mapping Classes to UDTs with XML

You can map UDTs to classes in app.config or web.config files. Oracle does provide a sample XML and short explanation on how to do this, but I still had to struggle with it a bit.

First of all, the important point is that XML mapping is used primarily and mapping through attributes and reflection as secondary, so you can leave the attributes into your classes and just add the following xml- snippet to your web.config:

<oracle.dataaccess.client>
        <settings>
            
            <add name="ReplyHeaderDefinition" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.ReplyHeaderDefinition,TestApp'
                    typeName='REPLY_HDR_DEF' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>
            
            <add name="GenericReplyOut" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.GenericReplyOut,TestApp'
                    typeName='GENERIC_REPLY_OUT' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>            
            
            <add name="GridColsTable" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.GridColsTable,TestApp'
                    typeName='GRIDCOLS_TBL' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>
            
            <add name="GridRowsTable" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.GridRowsTable,TestApp'
                    typeName='GRIDROWS_TBL' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>
            
            <add name="GridColsDefinition" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.GridColsDefinition,TestApp'
                    typeName='GRIDCOLS_DEF' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>
            
            <add name="GridRowsDefinition" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.GridRowsDefinition,TestApp'
                    typeName='GRIDROWS_DEF' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>

            <add name="ColumnValuesTable" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.ColumnValuesTable,TestApp'
                    typeName='COL_VALS_TBL' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>

            <add name="ColumValueDefinition" 
            value="udtMapping factoryName='TestApp.Data.DbTypes.ColumValueDefinition,TestApp'
                    typeName='COL_VALS_DEF' 
                    schemaName='TEST' dataSource='mydb.localhost'"/>
        </settings>
    </oracle.dataaccess.client>

Note that this does not completely disable the reflection and the need for attributes as the XML only maps the assembly and class to a specific UDT. It still needs to map the properties so you need to have the OracleObjectMapping- attributes.

Points of Interest

Mapping Oracle UDTs to C# classes can be a bit of a pain but it can be done and made to work with modern ASP.NET apps.

I do not know why Microsoft needs to emit code into IL directly, but luckily, this is not a showstopper for using UDTs and Oracle procedures from the app directly without needing to create some intermediate layer with WCF service or such.

I hope you might save a couple of days hitting the wall with my article.

Happy coding!