Forum Stats

  • 3,727,090 Users
  • 2,245,319 Discussions
  • 7,852,584 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,227 Gold Crown
    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

  • BluShadow
    BluShadow Member, Moderator Posts: 40,905 Red Diamond

    Well, for starters l_result is an object type. You haven't declared a variable of that type and instantiated the object, such that you could then query results in to that instantiated object variable. Remember with OO programming, the object type is just the blueprint of the object, it's not an object instantiation in it's own right.

  • Paulzip
    Paulzip Member Posts: 8,227 Gold Crown
    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>);

  • User_NHLO2
    User_NHLO2 Member Posts: 1 Green Ribbon
    Thank you so much!
    And what type should be assigned to the l_result variable?
    
    CREATE TYPE l_result AS OBJECT is this not instantiating an object? Sorry for the stupid enough questions ...
    


  • User_NHLO2
    User_NHLO2 Member Posts: 1 Green Ribbon
  • User_H3J7U
    User_H3J7U Member Posts: 69 Blue Ribbon

    A variable number of columns can be result of:

    • odcitable interface function
    • polymorphic table function
    • sql_macro(table) function

    But in any case the columns set determined on hard parse and cannot depend on the values from outer query or bind variables.

    create or replace function sm(fiction number) return varchar2 sql_macro as
    begin
      return 'select '''||to_char(systimestamp,'mi:ss.ff')||''' f'||nvl(to_char(fiction),'null')||' from dual';
    end;
    /
    
    alter session set cursor_sharing=exact;
    select * from sm(1);  -- hard parse on first execution
    F1
    22:38.138977000
    
    select * from sm(2);  -- hard parse on literal mismatch
    F2
    22:38.148748000
    
    select * from sm(1);  -- soft parse, query cached from first sm(1)
    F1
    22:38.138977000
    
    select * from sm(length(sysdate)); -- hard parse, value of sql expression is not defined
    FNULL
    22:39.568100000
    
  • BluShadow
    BluShadow Member, Moderator Posts: 40,905 Red Diamond


    Are you saying that the l_result object type will be different each time?

    And if the result is dynamically changing then what code is going to use the results? Certainly not standard PL/SQL code as it won't know what it's dealing with.

    How about going back to the beginning and explaining what it is you're actually required to achieve with some example data and expected results, and there may be a better way to do it than using dynamic code (which is often an indication of bad design)

  • User_NHLO2
    User_NHLO2 Member Posts: 1 Green Ribbon
    Yes, the number of columns will always be different due to the fact that draw_key will change, so for each draw_key there will be a different number of wincat_num. The FOR wincat_num IN (1,2,3,4,5) line should contain the result from the query:
    

    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 listagg(wincat_num ,', ') within group (order by wincat_num)

    from t

    where draw_key = 123456



    and in the end the result that I would like to get in the function is like this:


    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

    (

    select draw_key,wincat_num,win_amount,win_count

    from t

    where draw_key = 123456

    )

    PIVOT

    (

    sum(win_count) as win_count,sum(win_amount) as win_amount

    FOR wincat_num IN (1,2,3,4,5) --numbers from wincat_num

    )

  • User_NHLO2
    User_NHLO2 Member Posts: 1 Green Ribbon
    It turns out that somehow I need to declare a variable that will accept a table with n-number of columns ...
    


  • User_NHLO2
    User_NHLO2 Member Posts: 1 Green Ribbon
    Thank you!
    I know for sure that the column values ​​will be number, is it possible to somehow declare a table variable that will only accept number values ​​in the columns?
    


  • User_H3J7U
    User_H3J7U Member Posts: 69 Blue Ribbon
    is it possible to somehow declare a table variable that will only accept number values ​​in the columns?
    

    One column with multiple values:

    • collection - collect()
    • json - json_objectagg/json_arrayagg()
    • xml - xmlagg()
    • csv - listagg()
    • cursor expression - cursor()


Sign In or Register to comment.