iterate through pipelined function

[fosa]

Hello,

I create a pipelined function


CREATE OR REPLACE
TYPE EVENT_ROW_TYPE AS OBJECT (
ID_STOCK VARCHAR2(22),
SERVER_TIME DATE,
OPEN number,
LOW number,
HIGH number,
CLOSE number,
VOLUME number,
TRADE number
);
/

CREATE OR REPLACE
TYPE EVENT_TABLE_TYPE AS TABLE OF EVENT_row_type;
/



CREATE OR REPLACE FUNCTION F_DAILY_BAR
(
V_ticker IN VARCHAR2 )
RETURN EVENT_table_type PIPELINED
IS
BEGIN
DECLARE
v_max_tick data_event.id_stock%type;
v_max_price data_event.price%type;
v_max_vol data_event.volume%type;
v_max_time data_event.server_time%type;
v_min_price data_event.price%type;
v_min_tick data_event.id_stock%type;
v_min_time data_event.server_time%type;
v_volume data_event.volume%type;
v_close data_event.volume%type;
l_limit NUMBER := 1000;
type typ_tab_data
IS
TABLE OF barre_minute%ROWTYPE;
v_incoming typ_tab_data;
v_outgoing EVENT_ROW_TYPE;
l_done BOOLEAN;
CURSOR c2_cur
IS
SELECT id_stock,
dt ,
high ,
low ,
OPEN ,
CLOSE ,
volume ,
trade
FROM
( WITH dates AS
(SELECT (TRUNC(sysdate) + 8/24)+(LEVEL -1) /(24*60) dt
FROM DUAL CONNECT BY LEVEL <= 800
)
SELECT LAST_VALUE(id_stock IGNORE NULLS)OVER (PARTITION BY id_stock) id_stock ,
dt ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE low
END low ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE high
END high ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE OPEN
END OPEN ,
LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) CLOSE ,
LAST_VALUE(volume IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) volume ,
NVL(trade,0) trade
FROM dates
LEFT JOIN vw_barre_min m PARTITION BY (id_stock)
ON dates.dt = m.server_time
ORDER BY dt
)
WHERE id_stock=v_ticker;
BEGIN

SELECT id_stock,
price ,
TIME ,
volume
INTO v_max_tick ,
v_max_price ,
v_max_time ,
v_max_vol
FROM UTIL_HISTO_EVENT
WHERE id_stock=V_ticker;
SELECT TIME ,
id_stock,
price
INTO v_min_time,
v_min_tick,
v_min_price
FROM
(SELECT TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 TIME,
id_stock ,
price ,
volume ,
rank() over (partition BY id_stock order by TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 ASC ,price ASC, volume ASC ) rank
FROM mv_barre_event
GROUP BY id_stock,
price ,
volume ,
TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60
ORDER BY TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 ASC
)
WHERE rank =1
AND id_stock=v_ticker;
OPEN c2_cur;
LOOP
FETCH c2_cur bulk collect
INTO v_incoming limit l_limit;

l_done:= c2_cur%NOTFOUND;
FOR i IN 1..v_incoming.couNT
LOOP
IF (v_incoming(i).open IS NULL AND v_max_tick=v_incoming(i).id_stock AND v_incoming(i).id_stock=v_ticker) THEN
v_incoming(i).open := v_max_price;
v_incoming(i).close := v_max_price;
v_incoming(i).low := v_max_price;
v_incoming(i).high := v_max_price;
v_incoming(i).volume:=v_max_vol;
END IF;
IF (v_incoming(i).server_time=v_min_time AND v_incoming(i).id_stock=v_ticker) THEN
v_incoming(i).open := v_min_price;
END IF;
v_outgoing :=event_row_type(v_incoming(i).id_stock, v_incoming(i).server_time, v_incoming(i).open, v_incoming(i).low, v_incoming(i).close, v_incoming(i).high, v_incoming(i).volume, v_incoming(i).trade );
-- pipe row(event_row_type(r.id_stock,r.dt,r.open,r.low,r.high,r.close,r.volume,r.trade));
pipe row (v_outgoing);
END LOOP;
RETURN ;
END LOOP;
CLOSE c2_cur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END F_DAILY_BAR;
/

and it wotks
but if I want to iterate through it with a procedure I get a error

the procedure is
CREATE OR REPLACE procedure P_LIST_DAILY_BARRE_M
as
v_ticker data_event.id_stock%type;
begin
for x in ( select id_stock from ticker_tmp )
loop
-- dbms_output.put_line(x.id_stock);
DBMS_APPLICATION_INFO.SET_module( 'IN LOOP : '||x.id_stock,'');
execute immediate 'select * from table(F_DAILY_BAR(x.id_stock))';
end loop;
end;
/
I get an error :

SQL> exec p_list_daily_barre_m;
BEGIN p_list_daily_barre_m; END;

*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'
ORA-06512: at "P_LIST_DAILY_BARRE_M", line 9
ORA-06512: at line 1

Thanks

 

[jaxtell]

how about

execute immediate 'select * from table(F_DAILY_BAR(to_char(x.id_stock)))';

 

[Dagon]

If you want to do it from PL/SQL, you will need to use a cursor. I haven't got time to test it but I think it would be something like:

type t_curs is ref cursor;
v_curs t_curs;
open v_curs for 'select * from table(F_DAILY_BARid_stock))' using x.id_stock;
fetch v_curs into...;

 

[fosa]

Thanks a lot every body

I find something else by SQL !