CODEWITHSUNDEEP
asp.netoracle-database
ABC
ABC

Reputation: 181

Oracle query/ stored procedure to return multiple resultsets

I am using oracle database. When I tried to fetch the data using a single select query, it returned a single table in the dataset. How to write a select query or procedure in oracle, where I can get a dataset with 2-3(multiple) tables?

Upvotes: 1

Views: 12106

Answers (2)

ABC
ABC

Reputation: 181

This is exactly what I did, it was quite simple :

Database db = DatabaseFactory.CreateDatabase("ConnectionString");
object[] results = new object[3];
DbCommand cmd = db.GetStoredProcCommand("DATABASE.SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId",results);            
DataSet ds = db.ExecuteDataSet(cmd);
DataTable dt1 = ds.Tables[0];
DataTable dt2 = ds.Tables[1];

Upvotes: 2

Eggi
Eggi

Reputation: 1714

As far as I understood you question you would like to reduce the round trips to your database. This can be done by a stored procedure in the following way:

http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic6

Package header:

CREATE OR REPLACE PACKAGE SELECT_JOB_HISTORY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetJobHistoryByEmployeeId
(
    p_employee_id IN NUMBER,
    cur_JobHistory OUT T_CURSOR
);
END SELECT_JOB_HISTORY;

Package:

CREATE OR REPLACE PACKAGE BODY SELECT_JOB_HISTORY AS
PROCEDURE GetJobHistoryByEmployeeId
(
    p_employee_id IN NUMBER,
    cur_JobHistory OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_JobHistory FOR
    SELECT * FROM JOB_HISTORY
        WHERE employee_id = p_employee_id;

END GetJobHistoryByEmployeeId;
END SELECT_JOB_HISTORY;

Client:

// create connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =
    ParameterDirection.Output;

// open the connection and create the DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

// output the results and close the connection.
while(dr.Read())
{
    for(int i = 0; i < dr.FieldCount; i++)
        Console.Write(dr[i].ToString() + ";");
    Console.WriteLine();
}
conn.Close();

If you have to join these tables you can also use a normal join and split the result on the client (imho this is the way how a lot of ORMs do it).

Upvotes: 4

Related Questions