Using PIPELINED in PL/SQL

Pipelined functions are useful if there is a need for a data source other than a table in a select statement.

First, some preparation:

set arraysize 1
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

The first step is to define the format of the rows that are going to be returned. In this case here, we're going to return a date followed by a varchar2(99).

create or replace type o_date_varchar2 as object (
  d date,
  v varchar2(99)
)
/

Also, a collection type for the type previously defined must be created:

create or replace type t_date_varchar2 as table of o_date_varchar2
/

Finally, the function. It is a pipelined function as indicated by the keyword pipelined.

create or replace function f_date_varchar2(j in integer) return t_date_varchar2
  pipelined
as
  a_date_varchar2 o_date_varchar2 := o_date_varchar2(null,null);
begin
  for a in 1..j loop
    select  to_char(value),    sysdate 
      into  a_date_varchar2.v, a_date_varchar2.d 
      from  sys.v_ 
      where name ='bytes sent via SQL*Net to client';

    pipe row (a_date_varchar2);
    sys.dbms_lock.sleep(1);
  end loop;
  return;
end;
/

Here, we call f_date_varchar2, cast its return value as t_date_varchar2 (which is the return type of f_date_varchar2 and put the entire expression into a table(...) thingie. This table thingie will the feel like a table although it isn't.

select * from table(cast(f_date_varchar2(20) as t_date_varchar2));

Cleaning up:

drop function f_date_varchar2;
drop type t_date_varchar2;
drop type o_date_varchar2;

set arraysize 15

Pivot queries

Here's a table that assigns skills to users. A user can have 1 or more skills.

create table t_ (
  usr  Varchar2(10),
  skl  Varchar2(10)
);

The table is filled

insert into t_ values ('Jwa','skinning');
insert into t_ values ('Jwa','piercing');
insert into t_ values ('Jwa','tanning' );
insert into t_ values ('Jwa','writing');
insert into t_ values ('Jwa','reading');

insert into t_ values ('Rag','skinning');
insert into t_ values ('Rag','tanning' );
insert into t_ values ('Rag','molding');

insert into t_ values ('Qqq','swimming');

Now, we're interested in finding out which user has which skills. We desire to have on column per user. Following the steps outlined at the beginning of this page, we create a pipelined function:

create or replace type t_vc as table of varchar2(4000);
/

create or replace function pivot return t_vc pipelined
as
  v_last_usr t_.usr%type := null;
  v_line  varchar2(4000);
begin
  for r in (select usr, skl from t_ order by usr) loop
    if v_last_usr is null then
      v_line:=rpad(r.usr,11) || ': ';
      v_last_usr := r.usr;
    end if;
    if r.usr <> v_last_usr then
      pipe row(v_line);
      v_line:=rpad(r.usr,11) || ': ';
      v_last_usr := r.usr;
    end if;
    v_line := v_line || rpad(r.skl,11);
  end loop;
  pipe row(v_line);
  return;
end pivot;
/

And displaying the result:

select * from table(pivot);

And the result is:

Jwa        : skinning   piercing   writing    tanning    reading
Qqq        : swimming
Rag        : skinning   molding    tanning

Cleaning up:

drop table t_;
drop function pivot;
drop type t_vc;

See also pivot queries with Oracle and the pivot clause for alternative ways to achieve pivot results.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback