Skip to Main Content

SQL & PL/SQL

pipline function dynamic column display

User_NHLO2Apr 13 2021
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?
This post has been answered by Paulzip on Apr 13 2021
Jump to Answer
Comments
Post Details
Added on Apr 13 2021
10 comments
125 views