How can I retrieve multiple sys_refcursor using dapper and c#?

Question

I want to retrieve multiple sys_refcursors from oracle db using dapper. My PL/SQL code below

procedure GetData2 (p_result out sys_refcursor, p_result2 out sys_refcursor)
 is
 begin
   open p_result for
        select '1' col1, '2' col2 from dual
        union all
        select '11' col1, '22' col2 from dual;
        
   open p_result2 for
        select '3' col1, '4' col2 from dual;
 end;

And my c# code is below

using OracleConnection connection = new OracleConnection();
connection.ConnectionString = "";
connection.Open();
    
    
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add(name: "p_result", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.Add(name: "p_result2", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
    
var reader = connection.QueryMultiple("TEST_PACKAGE.GetData2", param: parameters, commandType: CommandType.StoredProcedure);
    
IEnumerable<MyClasss> dt = reader.Read<MyClasss>();
    
foreach (var item in dt)
{
    Console.WriteLine(item.col1);
}

This c# code only gives me p_result table result. How can I retrieve two sys_refcursor results using dapper?

Answer 1

You have to call a method to move to the next result in your data reader. The method is called .NextResult(). Try following code:

using OracleConnection connection = new OracleConnection();
connection.ConnectionString = "";
connection.Open();
    
    
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add(name: "p_result", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.Add(name: "p_result2", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
    
var reader = connection.QueryMultiple("TEST_PACKAGE.GetData2", param: parameters, commandType: CommandType.StoredProcedure);
    
IEnumerable<MyClasss> dt = reader.Read<MyClasss>();
    
foreach (var item in dt)
{
    Console.WriteLine(item.col1);
}
reader.NextResult();

IEnumerable<MyClasss> dt2 = reader.Read<MyClasss>();
    
foreach (var item in dt2)
{
    Console.WriteLine(item.col1);
}