Hello everyone.
Please help with advice. Given a table:
with t as (
select 123456 DRAW_KEY, 1 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 2 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 3 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 4 WINCAT_NUM,30000 WIN_AMOUNT,12 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 5 WINCAT_NUM,6000 WIN_AMOUNT,73 WIN_COUNT
from dual
)
select * from t
Based on this data, I create a function:
CREATE TYPE l_result AS OBJECT (
DRAW_KEY number,
WINCAT_NUM number,
WIN_AMOUNT number,
WIN_COUNT number
);
/
CREATE TYPE table_pip IS TABLE OF l_result;
CREATE OR REPLACE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS
v_query clob;
v_cols clob;
BEGIN
select listagg(wincat_num ,', ') within group (order by wincat_num)
into v_cols
from customers1
where draw_key = 123456; --v_draw_key!
v_query := '
SELECT * FROM
(
select draw_key,wincat_num,win_amount,win_count
from customers1
where draw_key = 123456
)
PIVOT
(
sum(win_count) as win_count,sum(win_amount) as win_amount
FOR wincat_num IN ('||v_cols||')
)';
EXECUTE IMMEDIATE v_query into l_result;
pipe row(l_result);
END;
But in the line:
CREATE OR REPLACE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS
throws errors:
PLS-00321: expression 'L_RESULT' is inappropriate as the left hand side of an assignment statement
and
PLS-00306: wrong number or types of arguments in call to 'L_RESULT'. most likely I incorrectly declared l_result, a call to the constructor is needed. Please tell me how can I rewrite the script with the correct l_result?