Re: table function help

From: rjamya <rjamya_at_gmail.com>
Date: Thu, 27 Dec 2012 11:04:54 -0500
Message-ID: <CAGurbTOnZDq5Aph4oRg0RWhy59g+jPMr7fR0bcwDwHhEnQT_SQ_at_mail.gmail.com>

change your query to
l_query varchar2(32000):='select redo_gen_format(b.inst_id,

b.sid,
b.serial#,
b.username,
b.machine,
b.osuser,
b.status,

round(a.value/1024/1024)) as my_row
from sys.gv_$sesstat a,
sys.gv_$session b
where a.statistic#=175
and a.inst_id=b.inst_id
and a.sid=b.sid ';

you are bulk collecting into an table of objects, so you must cast output of your select as an object, not individual columns.

Raj

On Thu, Dec 27, 2012 at 10:19 AM, Andrew Kerber <andrew.kerber_at_gmail.com>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.'
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 27 2012 - 17:04:54 CET