Friday, October 19, 2007

ODP.NET Tip: Using Pipelined Functions

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 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;
-- 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;
    -- 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);

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());
---------------- ---------------- ----------------
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;" +
    static void Main(string[] args)
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      // 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());
      // clean up
      // prevent console window from automatically closing
      Console.WriteLine("ENTER to continue...");