Hi, Andrew! Here is example of this: (formatted - https://gist.github.com/4391444 ) Some additions: 1. I created this without nds, but if needed you can use "execute immediate" instead of declared cursor 2. You can change cursor to parameterized one 3. I'm not sure that there is needed "bulk collect", as a function is pipelined, maybe would be better to fetch with limits 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); On Thu, Dec 27, 2012 at 7:19 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote: > Anyone good with table functions? I am getting an ora-00932 error when I > call this: > type redo_gen_format as object > (inst_id number, > sid number, > serial# number, > username varchar2(30), > machine varchar2(64), > osuser varchar2(30), > status varchar2(8), > redo_mb number); > > type redo_gen_type as table of redo_gen_format; > > pipeline function: > > CREATE or replace FUNCTION redo_gen > RETURN redo_gen_type PIPELINED > IS > out_data redo_gen_type; > l_query varchar2(32000):='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 '; > BEGIN > execute immediate l_query bulk collect into out_data ; > /* > for lvar in 1..out_data.count > loop > pipe row(out_data(lvar)); > end loop; > */ > END redo_gen; > / > > select * from table(redo_gen); > > ORA-00932: inconsistent datatypes: expected - got - > > ORA-06512: at "SYS.REDO_GEN", line 19 > ------------------------------ > > > -- > Andrew W. Kerber > > 'If at first you dont succeed, dont take up skydiving.' > > > -- > //www.freelists.org/webpage/oracle-l > > -- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank Tel: +7 903 207-1576 -- //www.freelists.org/webpage/oracle-l