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

    Forum Stats

  • 3,716,133 Users
  • 2,242,960 Discussions
  • 7,845,840 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Shaurav Raj
Shaurav Raj Member Posts: 6
edited August 2018 in ODP.NET

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

Shaurav Raj

Best Answer

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited August 2018 Accepted Answer

    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 RajShaurav Raj
  • Shaurav Raj
    Shaurav Raj Member Posts: 6
    edited August 2018

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited August 2018

    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 RajShaurav Raj
  • Shaurav Raj
    Shaurav Raj Member Posts: 6
    edited August 2018

    Thank you Alex. Really appreciate it.

This discussion has been closed.