-- selects values for frames #New 04.04.2018/J.Fes
SELECT
 listagg(fr_tbl.PVAL_0,',') within group (order by fr_tbl.PVAL_0)
FROM
infodba.PITEMREVISION IR
left join infodba.PS4_FRAMES fr_tbl on ir.puid = fr_tbl.puid
where IR.puid = :para_IrPuid



/*
-- Example using variable
declare v_res VARCHAR2(4000);
begin
SELECT
  listagg(fr_tbl.PVAL_0,',')  within group (order by fr_tbl.PVAL_0)
into v_res
FROM
infodba.PITEMREVISION IR
left join infodba.PS4_Frames fr_tbl on ir.puid = fr_tbl.puid
where IR.puid like '$$%' ;
 DBMS_OUTPUT.PUT_LINE(v_res);
end ;


-- oracle sql select multiple values into one column

SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;


As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

Start connect DB check:...TC.DB Open:OK
TC.DB:ServerVersion:[12.1.0.2.0 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options]

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;


*/
