Initialize an oracle table of records in EF core

Question

as the title says. here is the procedure in the package with the types:

create or replace PACKAGE          Pkg_Punt_Tijd
AS
  TYPE recTijd      IS RECORD (
                   TijdVan    DATE,
         TijdTot    DATE,
         Prioriteit NUMBER(1) ,
         Percentage NUMBER);
  TYPE curTijd         IS TABLE OF recTijd   INDEX BY BINARY_INTEGER;

  PROCEDURE sp_get_punt_tijd
  (
        i_PuntTijdVan       IN  DATE,
        i_PuntTijdTot       IN  DATE,
        i_ProjectNr      IN  VARCHAR2,
        i_DagType        IN  VARCHAR2,
        o_tijd           OUT curTijd,
        o_recnum           OUT NUMBER
  );

in C# i do this:

var p0 = new OracleParameter("i_PuntTijdVan", OracleDbType.Date, startDateTime, ParameterDirection.Input);
var p1 = new OracleParameter("i_PuntTijdTot", OracleDbType.Date, endDateTime, ParameterDirection.Input);
var p2 = new OracleParameter("i_ProjectNr", OracleDbType.Varchar2, projectNumber, ParameterDirection.Input);
var p3 = new OracleParameter("i_DagType", OracleDbType.Varchar2, dayType, ParameterDirection.Input);
var output = new OracleParameter("o_tijd", OracleDbType.Raw, "null", ParameterDirection.Output);
var output2 = new OracleParameter("o_recnum", OracleDbType.Decimal, "null", ParameterDirection.Output);
string command = "Pkg_Punt_Tijd.SP_GET_PUNT_TIJD(:i_PuntTijdVan, :i_PuntTijdTot, :i_ProjectNr, :i_DagType, :o_tijd, :o_recnum); END;";
await _context.Database.ExecuteSqlRawAsync(command, p0, p1, p2, p3, output, output2);
string? result = output.Value.ToString();
string? result2 = output2.Value.ToString();

as you can see i put the o_tijd parameter as type raw, but i have no idea how i can initialize it and get it back, normally it should give me an array of the record, so what is the best thing to do? make a class for o_tijd and ef will work it out? or i get it as string and parse it? and should i keep the parameter as raw or there is something else, documentations are so rare on oracle ef core. thanks,

score 0 · Answer 1 · answered Feb 18 '23 at 23:37

TYPE curTijd         IS TABLE OF recTijd   INDEX BY BINARY_INTEGER;

This is a collection type, and one only usable within PL/SQL. You can't bind that in any client program to a raw. You will need to research whether your Oracle client driver supports collections, and code accordingly. In some environments you can retrieve arrays into variables but not arrays of records - if that's the case in with yours, you can always break up the 4 columns of the recTijd type and pass back four separate arrays to variables in your client.

Initialize an oracle table of records in EF core

1 Answers1