pl/sql function return multiple columns

Question

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:

Not this:

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

What is the return type of the function? (Or is that your question?) — William Robertson
– William Robertson, Commented Oct 9, 2017 at 6:28
I think it should be datatable with 1 row. I'm new in plsql and sorry for my mistakes in question. I want to get exactly the same result after function call as first query in my question. One person said what in plsql I should use function for such thing and procedure is used for insert only. — Paul
– Paul, Commented Oct 9, 2017 at 6:33
I need to get not a single value, I want to get result with only one row and multiple columns. Function should give me a list of integers between parameter1=1 and parameter2=3 listed in a single row, so each integer is a new column and it should look like 1 2 3 — Paul
– Paul, Commented Oct 9, 2017 at 6:42
Are you saying you will have more than just 1, 2, and 3? You might also have 4, 5, etc... on the same row? — Bob
– Bob, Commented Oct 9, 2017 at 6:46
I'm not sure what a datatable is. You will need to define a suitable type, either in a package or in SQL using create type xxx as object (..). A function needs to return a specific type, or else maybe anydatatype or XML. — William Robertson
– William Robertson, Commented Oct 9, 2017 at 6:47

Andre · Accepted Answer · 2017-10-10 10:16:55Z

2

"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;

edited Oct 10, 2017 at 10:16

Andre

27.7k7 gold badges43 silver badges85 bronze badges

answered Oct 9, 2017 at 7:07

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

this function return <Cursor> after call select get_numbers (2,5) from dual. How can I get data from this?

Collectives™ on Stack Overflow

pl/sql function return multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related