.Net with Oracle : Working with Multiple Result Sets

Working with Multiple Result Sets

Oracle does not support batch queries, so you cannot return multiple result sets from a command. With a stored procedure, returning multiple result sets is similar to returning a single result set; you have to use REF CURSOR output parameters. To return multiple result sets, use multiple REF CURSOR output parameters.

The package spec that returns two result sets—all EMPLOYEES and JOBS records—follows:

CREATE OR REPLACE PACKAGE SELECT_EMPLOYEES_JOBS AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE GetEmployeesAndJobs (

    cur_Employees OUT T_CURSOR,   

    cur_Jobs OUT T_CURSOR

);

END SELECT_EMPLOYEES_JOBS;

The package body follows:

CREATE OR REPLACE PACKAGE BODY SELECT_EMPLOYEES_JOBS AS

PROCEDURE GetEmployeesAndJobs

(

    cur_Employees OUT T_CURSOR,

    cur_Jobs OUT T_CURSOR

)

IS

BEGIN

    -- return all EMPLOYEES records

    OPEN cur_Employees FOR

    SELECT * FROM Employees;

    -- return all JOBS records

    OPEN cur_Jobs FOR

    SELECT * FROM Jobs;

END GetEmployeesAndJobs;

END SELECT_EMPLOYEES_JOBS;

The following code shows how to fill two related tables in a DataSet using the two result sets returned from the above package:

// create the connection

OracleConnection conn = new OracleConnection("Data Source=oracledb;

    User Id=UserID;Password=Password;");

// define the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";

// add the parameters including the two REF CURSOR types to retrieve

// the two result sets

cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =

    ParameterDirection.Output;

cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =

    ParameterDirection.Output;

cmd.CommandType = CommandType.StoredProcedure;

// create the DataAdapter and map tables

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.TableMappings.Add("Table", "EMPLOYEES");

da.TableMappings.Add("Table1", "JOBS");

// create and fill the DataSet

DataSet ds = new DataSet();

da.Fill(ds);

// create a relation

ds.Relations.Add("EMPLOYEES_JOBS_RELATION",

   ds.Tables["JOBS"].Columns["JOB_ID"],

   ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);

// output the second employee (zero-based array) and job title

// based on the relation

Console.WriteLine("Employee ID: " +

    ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +

    "; Job Title: " +

    ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(

    "EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);

The console output shows the job title for the second employee:

Employee ID: 101; Job Title: Administration Vice President