How to call Oracle stored procedure which accepts RefCursor as out parameter using Entity Framework

Question

I have an Oracle stored procedure which accepts multiple out parameters of type RefCursor (code below).

create or replace 
    PROCEDURE PROCEDURE1(
    lookupvalues out SYS_REFCURSOR,
    lookupvalues1 out SYS_REFCURSOR)
AS 
BEGIN
    open lookupvalues for
    select attribute3 from table1 where attribute3 is not null;
    open lookupvalues1 for 
    select attribute1 from table2 where attribute1 is not null;
END PROCEDURE1;

I just need to get the result of those out parameter as data set using c# and Entity Framework.

I am able to get the data but I don't think it's a decent approach.

I want to use Entity Framework for retrieving the data (if possible).

using (APWCContext dbcontext = new APWCContext())
{
    using (var cmd = dbcontext.Database.Connection.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "PROCEDURE1";

        var businessUnit = new OracleParameter("lookupvalues", OracleDbType.RefCursor, ParameterDirection.Output);
        var currency = new OracleParameter("lookupvalues1", OracleDbType.RefCursor, ParameterDirection.Output);
        cmd.Parameters.AddRange(new[] { businessUnit,currency});                              
        cmd.Connection.Open();                    
        var reader= cmd.ExecuteReader();
        DataTable budt = new DataTable();                   
        while (reader.Read())
        {
            budt.Load(reader);
        }                                        
        var data = businessUnit.Value;                                                     
        cmd.Connection.Close();
    }
    //var businessUnit = new OracleParameter("lookupvalues", OracleDbType.RefCursor, ParameterDirection.Output);
    var r = dbcontext.GOODWILL_HEADER_STG.FirstOrDefault();

    //var result = dbcontext.Database.ExecuteSqlCommand("BEGIN PROCEDURE1(:lookupvalues); END;", businessUnit);
}

*" I don't think it's a decent approach"* What do you mean by this? What help are you expecting us to provide? — APC, Aug 01 '19 at 07:55
I mean when the data table is being populated, firstly the values returned by first ref cursor are added to first column of data table then after next read , values are copied from another cursor to another column of data table but starting from index where the first column ended.. — user2889674, Aug 04 '19 at 11:28
Sorry but it's still not clear. It would help if you posted some sample data - values of `table1` and `table2` - and your required output derived from that sample. — APC, Aug 04 '19 at 11:45
Okay, but according to you is it correct way of accessing the ref cursor values? — user2889674, Aug 04 '19 at 11:46
For certain values of "correct". You're calling ref cursors in a syntactically correct fashion. But whether it is *logically* correct depends on what you're trying to achieve. — APC, Aug 04 '19 at 11:53
My stored procedure is returning multiple attributes as i have shown in my question, i just want my web api to return all these attributes as json — user2889674, Aug 04 '19 at 11:58
Yes but you posted a question expressing doubt in your solution and asking us whether it's correct. We can't tell you if your solution is logically correct unless you explain the business logic you're trying to implement. Of course, you don't have to clarify your question, it depends on how keen you are to get a helpful answer. You appear not to like the one you have so far, and I think it's unlikely you'll get another unless you revise your question. But as I say, the choice is yours. — APC, Aug 04 '19 at 12:03
Yes i believe this approach is not the best that's why i mentioned this, returning the data is json is something that can be managed. I thought there might be someway by which i can get the values directly from out parameter instead of using the read(). — user2889674, Aug 04 '19 at 12:29

score 0 · Answer 1 · answered Aug 01 '19 at 09:45

Without knowing the scope of your requirement fully, it looks like your procedure is not necessary. Alternative question could be why do you want to use Entity Framework?

Please see the answer referenced here:

Returning datatable using entity framework

You only need to define your entity table table1 once and then use Linq to extract the information.

See modified code extracted from linked answer:

DataTable dt = new DataTable();
(from a1 in context.Table1.Where(i => i.Attribute3 != "").AsEnumerable()
 from a2 in context.Table1.Where(i => i.Attribute1 != "").AsEnumerable()
   select new
          {
            Attribute = a2.Attribute1 ?? a1.Attribute3
            //etc
          }).Aggregate(table, (dt, r) =>
          {
            dt.Rows.Add(r.Attribute);
            return dt;
          });

The table that i need to query is not part of the schema that i have, it is in some other schema, that is why using stored procedure. And using the RefCursor because i need the returned result as data set, Please let me know if i am doing something wrong here. — user2889674, Aug 01 '19 at 10:19

How to call Oracle stored procedure which accepts RefCursor as out parameter using Entity Framework

1 Answers1