pipeline example

freelists_pipelined_function_example

create or replace package pkg_redo_gen as
  cursor c is 
  select 
      b.inst_id,
      b.sid,
      b.serial#,
      b.username,
      b.machine,
      b.osuser,
      b.status,
      round(a.value/1024/1024) redo_mb
  from
      gv$sesstat a,
      gv$session b
  where
      a.statistic#=175
  and a.inst_id=b.inst_id
  and a.sid=b.sid;
  
  type redo_gen_type is table of c%rowtype;

  FUNCTION redo_gen RETURN redo_gen_type PIPELINED;
end pkg_redo_gen;
/
create or replace package body pkg_redo_gen as

  FUNCTION redo_gen RETURN redo_gen_type PIPELINED
  is
    out_data redo_gen_type;
  begin
    open c;
    fetch c bulk collect into out_data;
    close c;
    for lvar in 1..out_data.count loop
      pipe row(out_data(lvar));
    end loop;
  end;

END pkg_redo_gen;
/
select *
from table(pkg_redo_gen.redo_gen);