When using PL/SQL to return data to your ODP.NET client application typically that data is returned using a REF CURSOR or using PL/SQL Associative Arrays. I have written about using REF CURSORs here and about using PL/SQL Associative Arrays here.
However, another method is also available: using pipelined functions. Since the official documentation covers pipelined functions well, I will not go over them here.
The following simple PL/SQL code can be used to demonstrate this technique.
Note: I am using version 10.2.0.3 of all components in this sample.
-- the pl/sql package
create or replace package pipeline_test as
-- output rows will be of this type
type output_type is record (
first_name varchar2(16),
last_name varchar2(16),
instrument varchar2(16)
);
-- the type the function will return
type output_set is table of output_type;
-- the pipelined function that returns the rows
function get_rows return output_set pipelined;
end;
-- the pl/sql package body
create or replace package body pipeline_test as
function get_rows return output_set pipelined is
-- used to build each row
this_rec output_type;
begin
-- add row for John
this_rec.first_name := 'John';
this_rec.last_name := 'Lennon';
this_rec.instrument := 'Guitar';
pipe row(this_rec);
-- add row for Paul
this_rec.first_name := 'Paul';
this_rec.last_name := 'McCartney';
this_rec.instrument := 'Bass';
pipe row(this_rec);
-- add row for George
this_rec.first_name := 'George';
this_rec.last_name := 'Harrison';
this_rec.instrument := 'Guitar';
pipe row(this_rec);
-- add row for Ringo
this_rec.first_name := 'Ringo';
this_rec.last_name := 'Starr';
this_rec.instrument := 'Drums';
pipe row(this_rec);
return;
end;
end;
Once you have created the PL/SQL Package and Package Body you can test the functionality from within SQL*Plus as follows:
SQL> select * from table(pipeline_test.get_rows());
FIRST_NAME LAST_NAME INSTRUMENT
---------------- ---------------- ----------------
John Lennon Guitar
Paul McCartney Bass
George Harrison Guitar
Ringo Starr Drums
4 rows selected.
The output is precisely what we want: a "normal" table.
After having created the PL/SQL code and verifying it functions as expected, the ODP.NET code is equally straight-forward:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace PipelineTest
{
class Program
{
// connection string - change as appropriate
private const string constr = "User Id=markwill;" +
"Password=oracle;" +
"Data Source=otndemo;" +
"Pooling=false;" +
"Enlist=false";
static void Main(string[] args)
{
// create and open connection
OracleConnection con = new OracleConnection(constr);
con.Open();
// create and setup command
// the "table" keyword is used with the pipelined function
// in the pl/sql package body to get the rows
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from table(pipeline_test.get_rows())";
// get a data reader
OracleDataReader dr = cmd.ExecuteReader();
// loop through the data reader printing the data to the console
while (dr.Read())
{
Console.WriteLine("{0} {1} - {2}", dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
}
Console.WriteLine();
// clean up
dr.Dispose();
cmd.Dispose();
con.Dispose();
// prevent console window from automatically closing
Console.WriteLine("ENTER to continue...");
Console.ReadLine();
}
}
}