ADO.NET 3.5 Cookbook, 2nd Edition

3.19. Retrieving Multiple Result Sets Using the Oracle Provider

Problem

You have an Oracle package that returns multiple result sets for related tables as REF CURSOR data types. You want to load the data into a DataSet.

Solution

Use the data type OracleType.Cursor.

The solution creates a Command for an Oracle package CURSPKG that takes an EMPLOYEE_ID input parameter. The package calls a stored procedure that returns two result sets—HR.EMPLOYEES and HR.JOB_HISTORY for the specified employee—as Oracle REF CURSOR output parameters.

A DataAdapter is created from the Command, retrieves the result sets, and loads them into a DataSet. A relation is created between the tables. Results are output to the console.

The Oracle package used in the solution is shown in Example 3-24, and the package body is shown in Example 3-25.

Example 3-24. Package: CURSPKG

CREATE OR REPLACE PACKAGE CURSPKG
AS
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE GetEmployeeWithJobHistory (
    pEmployeeID IN NUMBER,
    rcEmployees OUT T_CURSOR,
    rcJobHistory OUT T_CURSOR);
END CURSPKG;

Example 3-25. Package body: CURSPKG

CREATE OR REPLACE PACKAGE BODY CURSPKG AS PROCEDURE GetEmployeeWithJobHistory ( pEmployeeID IN NUMBER, rcEmployees OUT T_CURSOR, rcJobHistory OUT T_CURSOR ) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = pEmployeeID; OPEN V_CURSOR2 FOR SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = pEmployeeID); rcEmployees ...