Re: table function help

  • From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • To: andrew.kerber@xxxxxxxxx
  • Date: Fri, 28 Dec 2012 00:16:20 +0400

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


Other related posts: