Procedure refcursor null and array empty — oracle-tech

    Forum Stats

  • 3,715,654 Users
  • 2,242,820 Discussions
  • 7,845,479 Comments

Discussions

Howdy, Stranger!

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

Procedure refcursor null and array empty

user10686431
user10686431 Member Posts: 4
edited January 2020 in ODP.NET

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.

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited January 2020

    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
    user10686431 Member Posts: 4
    edited January 2020

    Alex,

    I'm sending a use case

    image.png

    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
    user10686431 Member Posts: 4
    edited January 2020

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited January 2020

    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

    Alex Keh-Oracle
  • user10686431
    user10686431 Member Posts: 4
    edited January 2020

    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;

    }

Sign In or Register to comment.