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

    }

  }

}