Skip to Main Content
  • Questions
  • How To Pass a List Object From C# to an Oracle Stored Procedure?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ninja.

Asked: November 08, 2019 - 1:04 pm UTC

Answered by: Connor McDonald - Last updated: January 29, 2020 - 8:58 am UTC

Category: PL/SQL - Version: 11

Viewed 1000+ times

You Asked

Dear Sirs,
I know you’re probably tired from shaving your yak, but I’ve been doing research on this topic for a few months now with very little luck. Is there a way you can pass a list object from C# over to a stored procedure?

I was able to do something similar by using XML and multiple single arrays, but not with the entire list object. My list object contains several rows with several fields of various data types. I’m able to do this in SQL Server, but now that we are converting over to Oracle, I’m trying to find a way to accomplish this.

I'm currently using the following setup:

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

Here’s my C# code:

public class Automobile
        {
            public string Make { get; set; }
            public string Model { get; set; }
            public int 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);
            }
        }


Below is my Oracle code:

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     INT     NULL,
            Country  NVARCHAR2(100)   NULL,
            UserID   INT              NULL
        );

        CREATE OR REPLACE TYPE ot_Automobile AS OBJECT
        ( 
            Make varchar2(100),
            Model varchar2(100),
            Year int,
            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
        /


I know my code doesn’t work and I’m no expert, but any help would be greatly appreciated.

Thank you so much!

and we said...

I spoke to our .Net experts on this one.

For using a user defined type (UDT), you should use Oracle Developer Tools for Visual Studio to create a custom class. See the custom class section of this tutorial:
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/appdev/dotnet/userdefinedtypes/index.html

2) For using a PL/SQL table you may need to use a nested table or VARRAY, but if performance is a consideration, you should strongly consider changing to use Associative arrays rather than user defined types.

In particular, PL/SQL associative arrays are available from managed ODP.NET. UDTs can only be used by unmanaged ODP.NET (Oracle.DataAccess) at this time

You can also reach out to our experts directly at the ODP.NET forum

https://community.oracle.com/community/groundbreakers/database/developer-tools/windows_and_.net/odp.net

and you rated our response

  (2 ratings)

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

Reviews

This Didn't Answer My Question

January 24, 2020 - 8:22 pm UTC

Reviewer: Ninja Coder

Thank you for taking time out to reply. I know you guys are very busy keeping up with your exotic animals. My code sends a 2 dimensional array of values from the web service over to a stored procedure. Your example shows a Visual Studio wizard using forms application with straight SQL statements. We use stored procedures to avoid SQL injection.

I know my code doesn't work, hence the reason why I'm reaching out to you. If you have any code examples using a different approach to accomplish sending in a 2 dimensional array list of values from a web service to a stored procedure, please let me know.

Thanks again.
Chris Saxon

Followup  

January 27, 2020 - 1:50 pm UTC

My understanding of the example is it's using:

* UDTs (user-defined data types, e.g. create type...)
* Managed ODP.NET ManagedDataAccess

But:

UDTs can only be used by unmanaged ODP.NET (Oracle.DataAccess) at this time

So the code doesn't work because it's using unsupported functionality!

Either you need to change to unmanaged ODP.NET or pass the values as PL/SQL associative arrays (types declared in PL/SQL with the index by clause, e.g.:

declare
type arr is table of int index by pls_integer;
...

Outdated References

January 28, 2020 - 1:02 pm UTC

Reviewer: Ninja Coder

Thank you again for getting back to me so quickly. I know you guys are busy plucking your exotic peacocks, but in the .NET example provided, is using the Oracle Data Access Components (ODAC) 12.1.0.1.2, whereas in my example, I'm using Oracle.ManagedDataAccess 18.6.0. in which does NOT contain the "OracleDbType.Array".
Connor McDonald

Followup  

January 29, 2020 - 8:58 am UTC

but we do support array passing etc - its all there in the docs

https://docs.oracle.com/en/database/oracle/oracle-database/18/odpnt/featOraCommand.html#GUID-05A6D391-E77F-41AF-83A2-FE86A3D98872