ODP.Net Core Beta 3 with .Net Core and Dapper fails to pass and receive certain data types to Oracle

Shaurav Raj

Environment:

• .Net Core v.2.1
• Dapper ORM v.1.50.5
• Oracle ODP.Net Beta:3

Problem:

1. Passing variable and receiving objects from/to Oracle SP:

  a. ODP.Net does not seem to provide support for OracleDbTypes like

    - NUMBER

    - TABLE, etc.

    - It throws the error "wrong number of types or arguments". Even when I am passing the exact number of input and output parameters with exact names.

  b. The stored procedures on the other side are just receiving datatypes like NUMBER and are returning multiple TABLES as output parameter.

Code:

1. How I am including the parameters in the command:

    var sql = OracleStoredProcedures.ValidateZipcode;

                var parameters = new OracleDynamicParameters();

                parameters.Add("id_sth", OracleDbType.Int32, ParameterDirection.Input, zipcode);

                parameters.Add("id_sth2", OracleDbType.Int32, ParameterDirection.Input, zipcode);

                parameters.Add("input_str", OracleDbType.Varchar2, ParameterDirection.Input, "random string");

   

                parameters.Add("tdata", OracleDbType.RefCursor, ParameterDirection.Output);

                parameters.Add("tdata2", OracleDbType.RefCursor, ParameterDirection.Output);

               

                //parameters.Add("success_ful", OracleDbType.Varchar2, ParameterDirection.Output);

                //parameters.Add("error_message", OracleDbType.Varchar2, ParameterDirection.Output);

   

   

                var reader = SqlMapper.QueryMultiple(_unitOfWork.GetConnection(),

                    "TEST", parameters, commandType: CommandType.StoredProcedure);

                //var success = reader.Read<bool>();

                var result = reader.Read<Object>();

                var result2 = reader.Read<Object>();

                var result3 = reader.Read<string>();

**This code works when the SP is returning type REFCURSOR but it does not work on the SP's that are returning the TABLE on the same exact query.**

I am new to using Oracle with .net any help will be highly appreciated.

Thank you

Alex Keh-Oracle

The Oracle TABLE type is a user-defined type, which is currently not supported by ODP.NET Core. The TABLE type will not be supported in the first production release.

Alex Keh-Oracle

The Oracle TABLE type is a user-defined type, which is currently not supported by ODP.NET Core. The TABLE type will not be supported in the first production release.

Shaurav Raj

Hi Alex, Thank you so much for the response and your time. We have many clients who have been using Oracle since ever and are willing to move to .net core for API.

I was wondering what would be the best practice for .net core currently.

My limited knowledge of ODP.Net Core the only currently supported type that returns collection is REF-CURSOR.

Given that we have REF-CURSOR supported, Is it a better practice to return the output as a single OUTPUT of type REF-CURSOR with values like BOOLEAN and STRING (VARCHAR) and COLLECTION(REF_CURSOR) wrapped inside the all encompassing REF-CURSOR.

Or is it a better practice to return multiple OUTPUTS of type BOOLEAN and STRING(VARCHAR) along with COLLECTION(REF-CURSOR).

It might be too much to ask in the forum. Any suggestions or thought or If you can please point me to any resources it would be really really helpful.

Thank you for your time

Alex Keh-Oracle

You can use associative arrays in ODP.NET Core. However, these arrays only can store scalar data types, which means no REF Cursor. No other collection type is supported in ODP.NET Core.

Shaurav Raj

Thank you Alex. Really appreciate it.