Forum Stats

  • 3,768,508 Users
  • 2,252,800 Discussions
  • 7,874,600 Comments

Discussions

pipline function dynamic column display

User_NHLO2
User_NHLO2 Member Posts: 1 Green Ribbon
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?


Tagged:

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,490 Blue Diamond
    Accepted Answer

    Your code is bizarre and I've no idea why you are doing it like that.

    Anyway, for starters, you need a variable for the pipeline result:

    v_query clob;
    v_cols clob;
    v_result l_result;
    


    You also need to construct the object

    e.g.

    v_result := L_RESULT(<the four columns>);

Answers