Pipelined functions in Oracle

create or replace
function virtual_table( p_start number,
p_end number ) return virtual_table_type
pipelined as
begin
for i in p_start .. p_end loop
dbms_output.put_line( 'returning row ' || i );
pipe row(i);
end loop;
dbms_output.put_line( 'done...' );
return;
end virtual_table;
/
set serveroutput on
begin
for x in ( select *
from table( virtual_table( -2, 2 ) ) )
loop
dbms_output.put_line( 'printing from anonymous block ' ||
x.column_value);
end loop;
end;
/
create or replace
type elementType as table of varchar2(100)
/
create or replace
type rowType as object(
key number,
data elementType )
/
create or replace
type resultType as table of rowType
/
create or replace
function pivot( p_cursor in sys_refcursor )
return resultType pipelined as
l_key varchar2(4000);
l_data varchar2(4000);
l_last varchar2(4000);
l_row rowType;
begin
loop
fetch p_cursor into l_key, l_data;
exit when p_cursor%notfound;

if ( l_last is null or l_key <> l_last ) then
if ( l_row is not null ) then
pipe row( l_row );
end if;

l_row := rowType( l_key, elementType( l_data ) );
l_last := l_key;
else
l_row.data.extend();
l_row.data( l_row.data.count ) := l_data;
end if;
end loop;
if ( l_row is not null ) then
pipe row( l_row );
end if;
close p_cursor;
return;
end;
/
select *
from table( pivot( cursor( select deptno, ename
from emp
order by deptno ) ) )
/
select *
from table( pivot( cursor( select deptno, sal
from emp
order by deptno ) ) )
/