CODEWITHSUNDEEP
sqloracle-databaseplsql
Paul
Paul

Reputation: 47

pl/sql function return multiple columns

For example I have a query

select (select 1 from dual) res1
       , (select 2 from dual) res2
       , (select 3 from dual) res3 
from dual;

Result: 

res1 res2 res3
---- ---- ----
   1    2    3

I want to create function with loop from 1 to 3 which will return me same result, 3 columns in 1 row

 FOR i IN 1..3 LOOP

 END LOOP;

What should be in function body to achieve goal? I know I can use PIVOT to get this result in select query, but parameter of function is dynamic (numberFrom(1) and numberTo(3)), because of it I need loop

Pseudocode: Inside loop I have select query, which uses parameters in loop. And result of select should be a new column in final result

FOR i IN 1..3 LOOP
   select i from dual;
END Loop;

I want this:

enter image description here

Not this:

enter image description here

I can pass any integers as parameters. So if I pass 3 and 8 I should qet 3,4,5,6,7,8

Upvotes: 0

Views: 1969

Answers (1)

APC
APC

Reputation: 146209

"I can pass any integers as parameters. So if I pass 3 and 8 I should get 3,4,5,6,7,8"

You need Dynamic SQL for this.

create or replace function get_numbers 
   ( p_start in number, p_end in number )
    return sys_refcursor
is
    v_stmt varchar2(32767);
    n simple_integer := 0;
    rc sys_refcursor;
begin
    v_stmt := 'select ';
    for idx in p_start..p_end loop
        v_stmt := v_stmt || to_char( p_start + n);
        n := n+1;
        v_stmt := v_stmt || 'res_' || to_char( n);
        if p_start + n < p_end then 
            v_stmt := v_stmt || ',';
        end if;
    end loop;
    v_stmt := v_stmt || ' from dual';
    open rc for v_stmt;
    return rc;
end;

Upvotes: 2

Related Questions