Reputation: 21
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.
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);
[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; }
}
}
[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; }
}
The first cursor gets mapped correctly (It only has 1 row).
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.
Upvotes: 2
Views: 1556
Reputation: 173
Currently this is not supported by EF Core: https://github.com/dotnet/efcore/issues/8127 take a look at the link to find alternatives to this problem.
Upvotes: 1