I have the following method in C #
public static ProdutividadeBean GetProdutividade()
{
Console.WriteLine(CairuUtils.UserContext.getCodUsuario());
String strConnection = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srvoda-scan.cairunet.ad.br)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=SRV_JAPAO))); User Id=user;Password=pass;";
Console.WriteLine(strConnection);
Oracle.ManagedDataAccess.Client.OracleConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(strConnection);
try
{
con.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" TAB.RANK, ");
sb.AppendLine(" TAB.CODFUNC, ");
sb.AppendLine(" TAB.FUNCSEPARADOR AS NOME, ");
sb.AppendLine(" TAB.QTDEOS, ");
sb.AppendLine(" TAB.QTITENS, ");
sb.AppendLine(" TAB.PESO, ");
sb.AppendLine(" TAB.VOLUME, ");
sb.AppendLine(" TAB.VLVENDA, ");
sb.AppendLine(" TAB.MESA ");
sb.AppendLine(" FROM TABLE(PKG_EXPEDICAO_CAIRU.F_GET_ACOMP_PRODUCAO_OS( ");
sb.AppendLine(" to_date('14/02/2018', 'dd/mm/yyyy') ");
sb.AppendLine(" ,to_date('14/02/2018', 'dd/mm/yyyy') ");
sb.AppendLine(" ,'00:00' ");
sb.AppendLine(" ,'23:59' ");
sb.AppendLine(" ,'CONFERENTE' ");
sb.AppendLine(" ,'1,2,3,11' ");
sb.AppendLine(" , 514");//CairuUtils.UserContext.getCodUsuario()
sb.AppendLine(" ) ");
sb.AppendLine(" )TAB ");
// OracleCommand cmd = new OracleCommand(sb.ToString(), con);
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sb.ToString(), con);
Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
ProdutividadeBean p = new ProdutividadeBean();
Console.WriteLine("vai executar a porra agora....");
if (dr.HasRows)
{
dr.Read();
Console.WriteLine("entra aqui seu filha da puta");
p.Mesa = dr["MESA"].ToString();
p.Peso = dr["PESO"].ToString();
p.Qtitens = dr["QTITENS"].ToString();
p.Qtdeos = dr["QTDEOS"].ToString();
p.Valor = Convert.ToDouble(dr["VLVENDA"]).ToString();
p.Volume = dr["VOLUME"].ToString();
}
return p;
}
catch (Oracle.ManagedDataAccess.Client.OracleException e)
{
throw new Exception(e.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
This method fetches data from a function in the database. I am trying to get the data to be returned from the data in SQLNavigator, but I would like to know how to do this.
Here is my query that returns the data normally in SQL Navigator, including a Ctr + C of which is in the
SELECT
TAB.RANK,
TAB.CODFUNC,
TAB.FUNCSEPARADOR AS NOME,
TAB.QTDEOS,
TAB.QTITENS,
TAB.PESO,
TAB.VOLUME,
TAB.VLVENDA,
TAB.MESA
FROM TABLE(PKG_EXPEDICAO_CAIRU.F_GET_ACOMP_PRODUCAO_OS(
to_date('14/02/2018', 'dd/mm/yyyy')
,to_date('14/02/2018', 'dd/mm/yyyy')
,'00:00'
,'23:59'
,'CONFERENTE'
,'1,2,3,11'
, 514
)
)TAB
This returns me a line with 9 columns, as expected (In the database application)
I figured it could be a limitation of Oracle DataAcces in performing query in function with type return, so I updated it to the latest version and it still does not work. Does anyone know why this does not work?