table function help

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 27 Dec 2012 09:19:54 -0600
Message-ID: <CAJvnOJaNU2E75XzsiCDWNko=zHAZrT_pX9fz+iQ66fEbSbX5ug_at_mail.gmail.com>

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

Received on Thu Dec 27 2012 - 16:19:54 CET