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
@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.
– Pedro Tomaz
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.
– George Wurthmann
Why use a cursor? Why not a "simple query" (
SELECT x FROM...
)?– João Martins
@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.
– Pedro Tomaz
@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.
– Pedro Tomaz
It can be. Run tests directly in the Precedent with the same data you are using in the debugging application.
– George Wurthmann
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.
– Pedro Tomaz
@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.
– Pedro Tomaz
In oracle you have access to test and Debugger in the past?
– George Wurthmann
@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#?
– Pedro Tomaz
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.
– George Wurthmann
@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().
– Pedro Tomaz