I'm working on an API that calls an oracle stored procedure -I can't touch it- and returns two refcursors. The problem is that in my code, only the first cursor beign returned from the sp gets mapped, while the other one trows an InvalidOperationException.
My repository class
var sql = "BEGIN CCO_PKG_WHITDRAWREQUEST.OBT_OPCION_RET_DES_PRC(:pi_num_seguro_social , :pi_num_resolucion, :po_cur_valida_res, :po_cur_opciones); END;";
var parameters = new OracleParameter[]
{
new OracleParameter("pi_num_seguro_social", request.NSS),
new OracleParameter("pi_num_resolucion", request.NumResolucion),
new OracleParameter("po_cur_valida_res", OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("po_cur_opciones", OracleDbType.RefCursor, ParameterDirection.Output)
};
//This one gets mapped without problems.
var validaResCursor = await _context.CursorValidaRes.FromSqlRaw(sql, parameters).ToListAsync();
//This one throws the exception.
var opcionesCursor = await _context.CursorOpciones.FromSqlRaw(sql, parameters).ToListAsync();
var result = (opcionesCursor, validaResCursor);
The cursor's class that gets mapped
[Keyless]
public class ValidaResCursor
{
public string EsCVEValido { get; set; }
public string Opcion { get; set; }
public string Descripcion { get; set; }
public double SaldoA { get; set; }
public double SaldoB { get; set; }
public string Vigencia { get; set; }
}
}
The cursor's class that doesn't
[Keyless]
public class OpcionesCursor
{
public string cv { get; set; }
public string tipo { get; set; }
public string descripcion { get; set; }
public double monto { get; set; }
public double salario_bc { get; set; }
}
Execution
The first cursor gets mapped correctly (It only has 1 row).
Error
The second cursor throws an error.
This is the first time I'm working with oracle in a dotnet enviroment, but I'm following the oficial guide from oracle, (see. https://github.com/oracle/dotnet-db-samples/blob/master/samples/dotnet-core/ef-core/stored-procedure/return-ref-cursor.cs) and as you can see, aparently it only works with sp's that returns only one cursor. Any ideas? Any help will be appreciated.