How To Pass a List Object From C# to an Oracle Stored Procedure?

Ninja Coder picture Ninja Coder · May 8, 2019 · Viewed 7.7k times · Source

I'm trying to send a List Object from my C# WebService method over to my stored procedure in Oracle.

Before posting here, I've tried all suggested duplicate links. Here's what I've accomplished so far:

  • Success: In C#, I can pass my List values from my HTML page over to my WebService method.
  • Success: In Oracle, I have created a Table, Object Type, Table Type, and Stored Procedure to accept the List values. I was able to test this using an Anonymous block and sample data.
  • Problem: I cannot get to pass my List values from my C# WebMethod over to my Oracle Stored Procedure.

I'm currently using the following setup:

  • Visual Studio 2017
  • .NET Framework 4.6.1
  • Oracle.ManagedDataAccess 18.6.0

Keep in mind that the version of Oracle.ManagedDataAccess 18.6.0 does NOT contain the OracleDbType.Array as suggested in the older examples.


        public class Automobile
        {
            public string Make { get; set; }
            public string Model { get; set; }
            public string Year { get; set; }
            public string Country { get; set; }
        }
        using Oracle.ManagedDataAccess.Client;
        using Oracle.ManagedDataAccess.Types;

        [WebMethod(EnableSession = true)]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string InsertCars(List<Automobile> myCars, int userID)
        {
            DataSet dataSet = new DataSet();

            using (OracleConnection sqlConnection = new OracleConnection(OracleDBConnection))
            {
                using (OracleCommand sqlCommand = new OracleCommand("sp_InsertCars", sqlConnection))
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    sqlCommand.Parameters.Add(
                        new OracleParameter
                        {
                            CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                            Direction = ParameterDirection.Input,
                            ParameterName = "p_CarList",
                            UdtTypeName = "tt_Automobile",
                            Size = myCars.Count,
                            Value = myCars.ToArray()
                        }
                    );

                    sqlCommand.Parameters.Add(
                        new OracleParameter
                        {
                            OracleDbType = OracleDbType.Int32,
                            Direction = ParameterDirection.Input,
                            ParameterName = "p_UserID",
                            Value = userID
                        }
                    );

                    sqlCommand.Parameters.Add(
                        new OracleParameter
                        {
                            OracleDbType = OracleDbType.RefCursor,
                            Direction = ParameterDirection.Output,
                            ParameterName = "o_Cursor"
                        }
                    );

                    using (OracleDataAdapter sqlAdapter = new OracleDataAdapter(sqlCommand))
                    {
                        sqlAdapter.SelectCommand = sqlCommand;
                        sqlAdapter.Fill(dataSet);
                    }
                }

                return JsonConvert.SerializeObject(dataSet);
            }
        }

        CREATE TABLE tblCars
        (
            RecordID INT GENERATED BY DEFAULT  AS IDENTITY NOMINVALUE NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE NOORDER,
            Make     NVARCHAR2(100)   NULL,
            Model    NVARCHAR2(100)   NULL,
            Year     NVARCHAR2(4)     NULL,
            Country  NVARCHAR2(100)   NULL,
            UserID   INT              NULL
        );

        CREATE OR REPLACE TYPE ot_Automobile AS OBJECT
        ( 
            Make varchar2(100),
            Model varchar2(100),
            Year varchar2(4),
            Country varchar2(100)
        );

        CREATE OR REPLACE TYPE tt_Automobile AS TABLE OF ot_Automobile;

        CREATE OR REPLACE PROCEDURE sp_InsertCars 
        (
            p_CarList In tt_Automobile,
            p_UserID In integer,
            o_Cursor Out Sys_RefCursor
        )
        AS
        BEGIN
            DBMS_Output.Enable;

            For RowItem In (Select * From Table(p_CarList))
            Loop
            Insert Into tblCars 
            (
                Make, 
                Model, 
                Year, 
                Country, 
                UserID
            )
            Values(
                RowItem.Make,
                RowItem.Model,
                RowItem.Year,
                RowItem.Country,
                p_UserID
            );        
            End Loop;

            -- Return our results after insert
            Open o_Cursor For
            Select Make, Model, Year, Country From tblCars Where UserID = p_UserID;

        EXCEPTION
            When Others Then
            DBMS_Output.Put_Line('SQL Error: ' || SQLERRM);        

        END sp_InsertCars;

        COMMIT
        /

The result should allow me to pass my array Object from my WebService WebMethod over to my Oracle stored procedure and then loop through each item of the array to perform an Insert.

Here's an example of the data I'm trying to pass in.

Sample data being passed

Answer

User12345 picture User12345 · May 8, 2019

Please refer following link to setup ODAC Setup Ref and use follwing link to get the ODAC

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System;
using System.Data;

namespace Strace_CustomTypes
{
    class Program
    {
        static void Main(string[] args)
        {
            // Setup Ref - https://o7planning.org/en/10509/connecting-to-oracle-database-using-csharp-without-oracle-client
            // ODAC 64bit ODAC122010Xcopy_x64.zip - https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
            // .Net Framework 4

            // 'Connection string' to connect directly to Oracle.
            string connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SIT)));Password=PASSWORD;User ID=USERID";


            OracleConnection straceOracleDBConn = new OracleConnection(connString);
            OracleCommand cmd = new OracleCommand("PKG_TEMP.TEST_ARRAY", straceOracleDBConn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                straceOracleDBConn.Open();

                CustomVarray pScanResult = new CustomVarray();

                pScanResult.Array = new string[] { "hello", "world" };

                OracleParameter param = new OracleParameter();
                param.OracleDbType = OracleDbType.Array;
                param.Direction = ParameterDirection.Input;

                param.UdtTypeName = "USERID.VARCHAR2_ARRAY";
                param.Value = pScanResult;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                Console.WriteLine($"Error: {ex.Message} {Environment.NewLine} {ex.StackTrace}");
            }
            finally
            {
                straceOracleDBConn.Close();
                cmd.Dispose();
                straceOracleDBConn.Dispose();
            }

            Console.WriteLine("Press any key to exit");
            Console.ReadLine();
        }
    }

    //Ref https://www.codeproject.com/Articles/33829/How-to-use-Oracle-11g-ODP-NET-UDT-in-an-Oracle-Sto
    public class CustomVarray : IOracleCustomType, INullable
    {
        [OracleArrayMapping()]
        public string[] Array;

        private OracleUdtStatus[] m_statusArray;
        public OracleUdtStatus[] StatusArray
        {
            get
            {
                return this.m_statusArray;
            }
            set
            {
                this.m_statusArray = value;
            }
        }

        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

        public static CustomVarray Null
        {
            get
            {
                CustomVarray obj = new CustomVarray();
                obj.m_bIsNull = true;
                return obj;
            }
        }


        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, Array, m_statusArray);
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            object objectStatusArray = null;
            Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
            m_statusArray = (OracleUdtStatus[])objectStatusArray;
        }
    }

    [OracleCustomTypeMapping("USERID.VARCHAR2_ARRAY")]
    public class CustomVarrayFactory : IOracleArrayTypeFactory, IOracleCustomTypeFactory
    {
        public Array CreateArray(int numElems)
        {
            return new string[numElems];
        }

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

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