Hi Team,

how to read multiple result from a oracle stored procedure USING C#.  See the below scenario

Below is the Stored Procedure

CREATE OR REPLACE PACKAGE SAMPLEPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_TWO_CURSORS (EMPRESULT OUT T_CURSOR,DEPTRESULT OUT T_CURSOR);
END SAMPLEPKG;

CREATE OR REPLACE PACKAGE BODY SAMPLEPKG AS
PROCEDURE OPEN_TWO_CURSORS (EMPRESULT OUT T_CURSOR,DEPTRESULT OUT T_CURSOR)
IS
BEGIN
OPEN EMPRESULT FOR SELECT * FROM EmpTable;
OPEN DEPTRESULT FOR SELECT * FROM DeptTable;
END OPEN_TWO_CURSORS;
END SAMPLEPKG;

Below is the C# code.

We are using Oracle.ManagedDataAccess.dll

OracleDataReader reader =null;
OracleConnection connection =new OracleConnection("Some Oracle Connection String");
connection.open();
var command =new OracleCommand("SAMPLEPKG.OPEN_TWO_CURSORS",connection);
command.CommandType=CommandType.StoredProcedure;
command.Parameters.add("EMPRESULT",OracleDbType.RefCursor,ParameterDirection.Output);
command.Parameters.add("DEPTRESULT",OracleDbType.RefCursor,ParameterDirection.Output);
command.ExecuteReader();

//Tried below but nothing worked
var reader =(OracleDataReader)command.Parameters["EMPRESULT"].value;

var table1 =new DataTable();
OracleRefCursor ref1=(OracleRefCursor)command.Parameters["EMPRESULT"].value
table1.Load(ref1.GetDataReader());

C# code didnt work and it throws exception. Please help.