How (and when) to use PIPELINED PL/SQL functions in Oracle

There have been several cases where I needed to return a list of records after performing complex processing in Oracle.

I found out that the preferred method to do it is by using PIPELINED PL/SQL functions.

The last time I have done this was a few weeks ago where I had to “dig” into a log of user actions and extract information about user’s activity on a daily basis.

Other cases where I have used the same technique in the past were involving:

• Complex validations and return of validation results (warnings, errors, etc.).
• Tree walking and joining with other tables.

In all these cases I needed to query several tables and use temporary tables to collect intermediate results.

An example of the use of PIPELINED functions can be found here.

Here I summarize the steps must be taken when I have to implement something similar.

1.       Create an Object Type with the necessary fields.

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS AS OBJECT (
   user_nm            VARCHAR2 (16),
   act_dt             DATE,
   tot_proc           NUMBER (6),
   tot_in_progress    NUMBER (6),
   tot_cancelled      NUMBER (6),
   tot_failed         NUMBER (6),
   tot_compl          NUMBER (6),
   avg_assign_tm      NUMBER (6),
   median_assign_tm   NUMBER (6),
   start_tm           DATE,
   end_tm             DATE
);

2.       Create a collection

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS_COL AS TABLE OF typ_daily_user_totals;

3.       Implement a function returning a collection

CREATE OR REPLACE FUNCTION get_daily_user_totals(v_user_nm varchar2)
   RETURN typ_daily_user_totals_col PIPELINED
AS
   PRAGMA AUTONOMOUS_TRANSACTION;

   TYPE daily_totals_rec IS RECORD (
      act_dt             DATE,
      tot_proc           NUMBER (6),
      tot_in_progress    NUMBER (6),
      tot_cancelled      NUMBER (6),
      tot_failed         NUMBER (6),
      tot_compl          NUMBER (6),
      avg_assign_tm      NUMBER (6),
      median_assign_tm   NUMBER (6),
      start_tm           DATE,
      end_tm             DATE
   );

   TYPE daily_totals_tbl IS TABLE OF daily_totals_rec;
   daily_totals   daily_totals_tbl;
BEGIN

/*
   Do whatever is needed to collect your results, i.e. in a global temporary table
*/
 
   . . . . . . .

/*
   Collect your results in a PL/SQL table
*/
 
   SELECT   act_dt,
            tot_proc,
            tot_in_progress,
            tot_cancelled,
            tot_failed,
            tot_compl,
            avg_assign_tm,
            median_assign_tm,
            start_tm,
            end_tm
   BULK COLLECT INTO daily_totals
   FROM  g_daily_user_totals;

/*
   Commit or rollback to clean up the temporary tables and close the autonomous transaction
*/

   ROLLBACK;

/*
   Loop to pipe collected results
*/

   FOR i IN 1 .. daily_totals.COUNT ()
   LOOP

      PIPE ROW (NEW typ_daily_user_totals (
           v_user_nm,
           daily_totals (i).act_dt,
           daily_totals (i).tot_proc,
           daily_totals (i).tot_in_progress,
           daily_totals (i).tot_cancelled,
           daily_totals (i).tot_failed,
           daily_totals (i).tot_compl,
           daily_totals (i).avg_assign_tm,
           daily_totals (i).median_assign_tm,
           daily_totals (i).start_tm,
           daily_totals (i).end_tm));
   END LOOP;

   RETURN;
END;


4.       Select from the function

    SELECT user_nm, act_dt, tot_proc, tot_in_progress, tot_cancelled, tot_failed, tot_compl,
           avg_assign_tm, median_assign_tm, start_tm, end_tm
    FROM   TABLE (get_daily_user_totals(:aUser));

Some notes on the above:

1. If it is necessary to write into any table (as in the example above), then we must declare our function to work as an AUTONOMOUS TRANSACTION (see PRAGMA AUTONOMOUS_TRANSACTION).
2. The TABLE resulting from the select statement above can be joined with other tables and, possibly, create a view.
3. There have been cases where I have used a view (to make life of Java developers easier). In this case, if our PIPELINED function requires a parameter, then this can be passed via a context (using SYS_CONTEXT). I do not have an example ready for this, but I remember an article in Oracle Magazine describing this technique.