HI,

I have a question regarding the result set from ref cursors.

I have a package with different functions and some of them return ref cursors.

For example,

FUNCTION get_types_ranking ()
  RETURN SYS_REFCURSOR   
  IS
  o_cursor SYS_REFCURSOR;
  BEGIN
  OPEN o_cursor FOR
      SELECT rank, amount, v_type
  FROM (
  SELECT amount, v_type, dense_RANK() OVER(ORDER BY amount desc) as rank 
  FROM (
  SELECT 10 as amount, 'type1' as v_type from dual
  union
  SELECT 12, 'type2' from dual
  union
  SELECT 11, 'type3' from dual)
  )
  where rank <=5;
 RETURN o_cursor;
 END;  

The result of the cursor is:

RANK AMOUNT V_TYPE
1 12 type2
2 11 type3
3 10 type1

I need to show the ranking <=5. If the ranking is less than 5, then the result set should be

RANK AMOUNT V_TYPE
1 12 type2
2 11 type3
3 10 type1
4 null NTA
5 null NTA

I made the solution by using pipeline function. In general it looks like:

    TYPE t_rec IS RECORD (instr_rank number, v_type varchar2(20 char), v_value number);
    TYPE t_tab IS TABLE OF t_rec;

  FUNCTION combine_cur () 
    return t_tab pipelined IS
     type t_tmp_tab is table of t_rec index by pls_integer;
     l_tab t_tmp_tab;
    -- for the cursors returned by the functions
    l_cursor sys_refcursor;
    l_cursor2 sys_refcursor;
    -- for the individual columns from the cursors
    instr_rank number;
    v_type varchar2(20 char);
    v_value number;
    l_cnt number;
    SQL_query varchar2(30000);
    l_nr_rank number;
 BEGIN
  l_nr_rank := 5; --should show only 5 ranks
  l_cursor := get_types_ranking();
  loop
    fetch l_cursor into instr_rank, v_type, v_value;
    exit when l_cursor%notfound;
    l_tab(instr_rank).instr_rank := instr_rank;
    l_tab(instr_rank).v_type := v_type;
    l_tab(instr_rank).v_value := v_value;
    l_cnt := l_cursor%ROWCOUNT;
  end loop;
  close l_cursor;

  --DBMS_OUTPUT.PUT_LINE(l_cnt);
  --in case we have fewer ranks than 5, we should add others with NTA
 IF l_cnt < l_nr_rank then 
  while (l_cnt<l_nr_rank) loop
   SQL_query := 'select ' || to_char(l_cnt + 1) || ' as rank, ''NTA'' as v_type, null as v_value FROM DUAL';
   l_cnt:= l_cnt + 1;
  open l_cursor2 for sql_query;
    fetch l_cursor2 into instr_rank, v_type, v_value;
    exit when l_cursor2%notfound;
    l_tab(l_cnt).instr_rank := l_cnt;
    l_tab(l_cnt).v_type := v_type;
    l_tab(l_cnt).v_value := v_value;
  end loop;
  close l_cursor2;
 end if;

  for i in l_tab.first..l_tab.last loop
    pipe row (l_tab(i));
    --dbms_output.put_line(l_tab(i).rank ||','|| l_tab(i).v_type ||','|| l_tab(i).v_value);
  end loop;
  END combine_cur;
  
  -------------------
   FUNCTION get_cursor () 
    return sys_refcursor IS
   l_cursor sys_refcursor;
  BEGIN
    open l_cursor for
      select * from table(combine_cur());
    return l_cursor;
  END combo_cur;
  ----------------------------------

This solution works for me, but the problem arises when I have other ranking functions with the different set
of data. For example,

I need to show the ranking <=7. The cursor returns only 4 ranks. If the ranking is less than 7, then the result set
should be

TYPE   Rank   AMOUNT RATE  Balance
TYPE1  1 10 1,0 2
TYPE2  2 8 1,5 3
TYPE3  3 5 1,3     1 
TYPE4  4 3 1,2     2 
NOT   5 NULL NULL    NULL
NOT   6 NULL NULL NULL
NOT   7 NULL NULL NULL

Maybe there exists some other approach to achieve the right results by reusing the code (so that I should
not re-write the combine_cur() and get_cursor() for the every new case)?

Thank you for your attention.

var row_count number;
exec :row_count := 5;

with rws as (
  select level rn from dual
  connect by level <= :row_count
), ranked as (
  select amount, v_type, row_number() over(order by amount desc) as rk
  from (
    select 10 as amount, 'type1' as v_type from dual
    union
    select 12, 'type2' from dual
    union
    select 11, 'type3' from dual
  )
)
  select * from rws
  left   join ranked 
  on     rn = rk
  order  by rn;
  
RN    AMOUNT    V_TYPE    RK       
    1        12 type2             1 
    2        11 type3             2 
    3        10 type1             3 
    4    <null> <null>       <null> 
    5    <null> <null>       <null> 
  
exec :row_count := 7;

with rws as (
  select level rn from dual
  connect by level <= :row_count
), ranked as (
  select amount, v_type, row_number() over(order by amount desc) as rk
  from (
    select 10 as amount, 'type1' as v_type from dual
    union
    select 12, 'type2' from dual
    union
    select 11, 'type3' from dual
  )
)
  select * from rws
  left   join ranked 
  on     rn = rk
  order  by rn;
  
RN    AMOUNT    V_TYPE    RK       
    1        12 type2             1 
    2        11 type3             2 
    3        10 type1             3 
    4    <null> <null>       <null> 
    5    <null> <null>       <null> 
    6    <null> <null>       <null> 
    7    <null> <null>       <null>