(Oracle PL/SQL Sample) Function to output the vertical results into one row (horizontal)

I've created the function to print the vertical query results into one row horizontally with comma(,) seperated.

 

 

As you can see above, the results of ENAME query are displayed in vertical way.

I want to print the results like this : SMITH, ALLEN, WARD, ... 

 

 
create or replace function f_output_horizontal(v_sql in varchar2) return varchar2
is 
   type c_refcur is ref cursor;
   c_sel c_refcur;
   v_ret varchar2(2000) := '(';
   v_val varchar2(50);
begin
 
   open c_sel for v_sql;
 
      loop
         fetch c_sel into v_val;
         exit when c_sel%notfound;
         
         if v_ret = '(' then
            v_ret := v_ret || v_val;
         else
            v_ret := v_ret || ', ' || v_val;
         end if;
      end loop;
   close c_sel;
   
   return v_ret || ')';
end;
/
 

 

The results of execution of this function like below..

In oracle SQL*Plus, you have to put command "set serveroutput on" in order to display the results of the function execution.

 

 

In the above example, I've put SQL Query directly as an argument of the function f_output_horizontal().

If your SQL Query is long, you'd better using variable. This is the example.