Error running Oracledatareader.Hasrows with cursor without values

Asked

Viewed 38 times

1

I am using Oracledatareader to insert elements of a cursor into a list. The cursor is returned from an oracle db procedure. The problem is that when I have no elements returned on the cursor, the application stops working without error. I want to know how to check if the cursor has elements?

This is the function code that creates the list from the elements returned on the cursor and is in c# Asp.net.

 public List<VagaModel> BuscarVagas(string codCentroCusto, string anoPeriodo, string codPeriodo)
    {

        List<VagaModel> lstVagas = null;

        using (OracleConnection con = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = con.CreateCommand())
            {
                try
                {
                    cmd.CommandText = "NET_BUSCAR_VAGAS_P1";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("vCodCentroCusto", OracleDbType.Int64).Direction = ParameterDirection.Input;
                    cmd.Parameters["vCodCentroCusto"].Value = Convert.ToInt64(codCentroCusto);

                    cmd.Parameters.Add("vAnoPeriodo", OracleDbType.Int32).Direction = ParameterDirection.Input;
                    cmd.Parameters["vAnoPeriodo"].Value = Convert.ToInt32(anoPeriodo);

                    cmd.Parameters.Add("vCodPeriodo", OracleDbType.Int64).Direction = ParameterDirection.Input;
                    cmd.Parameters["vCodPeriodo"].Value = Convert.ToInt64(codPeriodo);


                    cmd.Parameters.Add("vCursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                    con.Open();


                    cmd.ExecuteNonQuery();


                    OracleRefCursor cursor = (OracleRefCursor)cmd.Parameters["vCursor"].Value;



                    using (OracleDataReader dr = cursor.GetDataReader())
                    {
                        if (dr.HasRows)
                        {
                            lstVagas = new List<VagaModel>();

                                while (dr.Read())
                                {
                                    var codVaga = dr.GetValue(0);
                                    var qtdSolteiro = dr.GetValue(1);
                                    var qtdCasal = dr.GetValue(2);
                                    var qtdBeliche = dr.GetValue(3);
                                    var qtdAuxiliar = dr.GetValue(4);
                                    var capVaga = dr.GetValue(5);
                                    var codTbVaga = dr.GetValue(6);

                                    VagaModel vaga = new VagaModel(codVaga.ToString(), qtdSolteiro.ToString(), qtdCasal.ToString(), qtdBeliche.ToString(),
                                        qtdAuxiliar.ToString(), capVaga.ToString(), codTbVaga.ToString());

                                    lstVagas.Add(vaga);
                                }
                        }

                        //dr.Close();
                        //dr.Dispose();

                    } 

                }
                catch (Exception e)
                {

                    throw new Exception(e.Message);
                }
                finally
                {


                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
        }

        return lstVagas;
    }
  • puts the stacktrace for kindness

  • @Lucas Miranda No error. The application stays in Hasrows as if it were an infinite loop. In fact the application keeps trying to access the Hasrows method and does not leave nor the error.

  • You can not say the problem without the error, but as you have no mistake I will leave a tip. Have you tried using only dr.Read()? It will return false if there are no lines, you don’t even need Hasrows in this case.

  • 1

    Why use a cursor? Why not a "simple query" (SELECT x FROM...)?

  • @Joãomartins I don’t understand much about oracle yet, however, the BD is third they passed me only the procedures. Then I mount the Parameters and hope to receive a cursor as Ouput. So I use the Reset dataset to insert the cursor values into a list. Please correct me if I’m wrong. Thank you.

  • @Georgewurthmann I have already used dr. Read() and the same thing happens. Could it be an error in the execution of the trial? It’s because I don’t have access to the comic book, I only have the procedures to use the data.

  • It can be. Run tests directly in the Precedent with the same data you are using in the debugging application.

  • I was able to identify that this case happens when Procedure does not return data. However, how to see if you got a return or not? Both with dr.Read(), and with dr.Hasrows, if no return was obtained, the application keeps accessing the method until it timeout and follow the next steps.

  • @Georgewurthmann Can you tell me if there is a way to identify from the trial nothing returns? I believe that’s the point to solve my problem.

  • In oracle you have access to test and Debugger in the past?

  • @Georgewurthmann I’m not allowed to debug in the past, but I can run and analyze the return. This proc lists available vacancies for scheduling. My problem is that if there are no vacancies available, proc gets no return. I believe it is at this point that the dataset tries to access the return of proc but has no return. I have tried with datatable and dataAdapter and the same problem occurs. Do you know any way to check if proc has return or not, in c#?

  • Don’t you have a BD dev environment so you can run/debuggar/change Procedure? The ideal is to identify why no return comes, at some point should give an Exception and this is untreated, return a value where you can identify that error occurred would be ideal.

  • @Georgewurthmann I agree with your placement, but the client will not provide this access and the information I have is that the Procedure returns a cursor if it has data to display, if it does not have data to display, the proc ends without returning the cursor and data. So I’m having a little trouble working on this exception. What I need is some way to check if proc returned anything before running Read().

Show 8 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.