Retrieve Record Set From Oracle Cursor

Oracle stored procedure cannot output a record set simply with a select statement as with SQL Server. It returns REF Cursor type of data through OUT parameters.(List 1 shows an oracle sp and List 2 shows C# code to access the cursors)

List 1: A stored procedure that outdates REF CURSORs
CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;

CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
BEGIN
OPEN EMPCURSOR FOR SELECT * FROM DEMO.EMPLOYEE;
OPEN DEPTCURSOR FOR SELECT * FROM DEMO.DEPARTMENT;
END OPEN_TO_CURSORS;
END CURSPKG;

List 2: C# code to retrieve the cursors
oraConn.Open();

OracleCommand cursCmd = new OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn);
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataReader reader = cursCmd.ExecuteReader();

while (reader.Read())
Console.WriteLine("{0}, {1}, {2}", reader.GetOracleNumber(0), reader.GetString(1), reader.GetString(2));

reader.NextResult();

while (reader.Read())
Console.WriteLine("{0}, {1}", reader.GetOracleNumber(0), reader.GetString(1));

reader.Close();
oraConn.Close();

The NextResult method of the OracleDataReader switches cursor to the next. The REF CURSOR parameters should be returned in order to avoid confusion.


In addition to DataReader, we can also use DataAdapter to fill cursors into a dataset (List 3).

List 3: Retrieve cursors with DataReader
DataSet ds = new DataSet();

OracleDataAdapter adapter = new OracleDataAdapter(cursCmd);
adapter.TableMappings.Add("Table", "Employees");
adapter.TableMappings.Add("Table1", "Departments");

adapter.Fill(ds);

Note the mapping from database source table to .Net DataTable object within a dataset. Source tables are identified by "Table", "Table1", "Table2" ... according to the order the REF CURSOR comes in the SQL code.