Procedure refcursor null and array empty

user10686431

I have a query via procedure in oracle where he returns me a refcursor with some blank columns (null), so far so good always did so and never had problems, however from the version of

.NET core 3.1 with Oracle.EntityFrameworkCore 2.19. 60 columns that should come blank (null) are coming with {} as if they were a blank object. How to solve it?

Thanks.

Alex Keh-Oracle

I haven't seen this issue before. Do you have a simple test case that reproduces the problem? Are you using EF Core 3?

user10686431

Alex,

I'm sending a use case

Return Json:

[

  {

    "ID_FAIXA": 1,

    "FAIXA_INICIAL": {},

    "FAIXA_FINAL": 89.999,

    "TARGET": 0

  },

  {

    "ID_FAIXA": 2,

    "FAIXA_INICIAL": 90,

    "FAIXA_FINAL": 99.999,

    "TARGET": 50

  },

  {

    "ID_FAIXA": 3,

    "FAIXA_INICIAL": 100,

    "FAIXA_FINAL": 104.999,

    "TARGET": 100

  },

  {

    "ID_FAIXA": 4,

    "FAIXA_INICIAL": 105,

    "FAIXA_FINAL": 109.999,

    "TARGET": 110

  },

  {

    "ID_FAIXA": 5,

    "FAIXA_INICIAL": 110,

    "FAIXA_FINAL": 112.499,

    "TARGET": 120

  },

  {

    "ID_FAIXA": 6,

    "FAIXA_INICIAL": 112.5,

    "FAIXA_FINAL": 124.999,

    "TARGET": 125

  },

  {

    "ID_FAIXA": 7,

    "FAIXA_INICIAL": 125,

    "FAIXA_FINAL": 137.499,

    "TARGET": 150

  },

  {

    "ID_FAIXA": 8,

    "FAIXA_INICIAL": 137.5,

    "FAIXA_FINAL": 149.999,

    "TARGET": 175

  },

  {

    "ID_FAIXA": 9,

    "FAIXA_INICIAL": 150,

    "FAIXA_FINAL": {},

    "TARGET": 200

  }

]

Query:

public async Task<DbDataReader> ExecutaProcedureAsync(string storeProcedureName, OracleParameter[] oracleParameterValues, ConfiguracoesOracle.Linguagem linguagemConfigOracle = ConfiguracoesOracle.Linguagem.Americano)

        {

            try

            {

                if (Con.State == ConnectionState.Closed)

                    Con.Open();

                Transaction = Con.BeginTransaction(IsolationLevel.ReadCommitted);

                var globalOracle = ConfiguracoesOracle.SetOracleGlobalization(Con.GetSessionInfo(), linguagemConfigOracle);

                if (globalOracle != null)

                    Con.SetSessionInfo(globalOracle);

                using (Cmd = new OracleCommand(storeProcedureName, Con)

                {

                    CommandType = CommandType.StoredProcedure,

                    Transaction = Transaction

                })

                {

                    if (Cmd.Parameters.Count == 0)

                    {

                        Cmd.Parameters.Clear();

                        if (oracleParameterValues != null)

                        {

                            foreach (var p in oracleParameterValues)

                            {

                                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))

                                {

                                    p.Value = DBNull.Value;

                                }

                                Cmd.Parameters.Add(p);

                            }

                        }

                    }

                    var odr = await Cmd.ExecuteReaderAsync();

                    Transaction.Commit();

                    return odr;

                }

            }

            catch (Exception ex)

            {

                Transaction?.Rollback();

                var param = new

                {

                    parametros = oracleParameterValues?.Where(s => s.Direction == ParameterDirection.Input).ToDictionary(l => l.ParameterName, l => l.Value),

                    procedure = storeProcedureName

                };

                ex.Data["parametros"] = JsonConvert.SerializeObject(param);

                throw ex;

            }

        }

According to one site, they say it's a new feature of C # 8? it will be? Can you disable this? Because we use a refcursor.

user10686431

Use:

.Net core 3.1

C# 8

Oracle.entityframeworkcore 2.19.60

I found that too. Has something to do with the new version? How to disable?

https://csharp.christiannagel.com/2018/06/20/nonnullablereferencetypes/

Alex Keh-Oracle

This tutorial looks like what you need to enable/disable nullable reference types:

https://docs.microsoft.com/en-us/dotnet/csharp/tutorials/nullable-reference-types

user10686431

Alex,

Great reference friend. I did a basic test as the site mentioned and it worked perfectly, but when we use with object Datareader it keeps bringing the empty object. With dataredader is not working. See the example of the code we implemented. Do we need to do something or is this already suspected of a bug?

public async Task<DbDataReader> ExecutaCmdRetornoAsync(string sql)

{

var cmd = Con.CreateCommand();

cmd.CommandText = sql;

var reader = await cmd.ExecuteReaderAsync();

return reader;

}

public OracleDataReader ExecutaCmdRetorno(string sql)

{

var cmd = Con.CreateCommand();

cmd.CommandText = sql;

var reader = cmd.ExecuteReader();

return reader;

}