Returning multiple record sets from Oracle

When you need to retrieve complex data shapes from a database, you traditionally have been restricted to either dealing with multiple (and possibly sparse) rows per entity, or else performing multiple calls to the database and iterating the results to create your object graph.

The former CAN be pretty performant, but is a pain to implement the graph generation state-machine.

The latter suffers from the latency involved in setting up multiple database calls.

What’s needed is the ability to retrieve multiple data sets, but with just ONE database call.

SQL Server trumpets this as a feature, but Oracle’s been able to do this for a while as well. The problem has been that it’s very badly documented how you access the data through ODP.Net. Now I’ll show you how.

Let’s start by creating a stored proc that returns some data from 3 independent tables:

 

    PROCEDURE PRO_GET_MULTIPLE
    (
     p_first OUT SYS_REFCURSOR,
     p_second OUT SYS_REFCURSOR,
     p_third OUT SYS_REFCURSOR
    ) IS
    BEGIN
         OPEN p_ first FOR
              select * from table_one;
         OPEN p_ second FOR
              select * from table_two;

         OPEN p_ third FOR
              select * from table_three;
    END;

 

Easy enough PL./SQL there. Let’s now retrieve the data using ODP.Net:

 

            OracleDatabase db = OracleDatabaseFactory.CreateDatabase ();

 

            OracleCommand cmd = new OracleCommand("MY_PACKAGE.PRO_GET_MULTIPLE", connection);

            cmd.CommandType = CommandType.StoredProcedure;

 

            OracleParameter staffTypeResult = new OracleParameter();

            staffTypeResult.ParameterName = "p_first";

            staffTypeResult.OracleDbType = OracleDbType.RefCursor;

            staffTypeResult.Direction = ParameterDirection.Output;

 

            OracleParameter nameTypeResult = new OracleParameter();

            nameTypeResult.ParameterName = "p_second";

            nameTypeResult.OracleDbType = OracleDbType.RefCursor;

            nameTypeResult.Direction = ParameterDirection.Output;

 

            OracleParameter identifierTypeResult = new OracleParameter();

            identifierTypeResult.ParameterName = "p_third";

            identifierTypeResult.OracleDbType = OracleDbType.RefCursor;

            identifierTypeResult.Direction = ParameterDirection.Output;

 

            cmd.Parameters.Add(staffTypeResult);

            cmd.Parameters.Add(nameTypeResult);

            cmd.Parameters.Add(identifierTypeResult);

 

            //Return the filled Dataset

            DataSet dataset = db.ExecuteDataSet(cmd);

 

Note how we add output parameters for each of the tables the stored procedure returns? This is where the magic lies – the OracleDatabase object knows how to fill a dataset from multiple out-parameters via the ExecuteDataSet command.

Now I should point out that the OracleDatabase type is our customised Enterprise Library Data Access Block provider, so your mileage may vary with other EntLib Oracle providers.

Once you’ve got a DataSet, of course, you can iterate it in the usual manner:

 

            DataTable table = dataset.Tables[tableIndex];

 

            int index = 0;

            foreach (DataRow row in table.Rows)

            {

                Console.Write(string.Format("{0} : {1} {2} {3}",

                    index, row[0], row[1], row[2]));

                Console.WriteLine();

                index++;

            }

 

And that’s all there is too it!