TRANSPOSE ROWS TO COLUMNS AND CONVERT TO COMMA SEPRATED VALUES (CSV)

/*Below is the basic sql to transpose rows to columns and concatenate by ','
The key here is using hierarchical queries and oracle supplied sys_connect_by_path function
*/
WITH s1 AS
( SELECT 'A' as col1
FROM dual
UNION
SELECT 'B' as col1
FROM dual
UNION
SELECT 'C' as col1
FROM dual)
SELECT SUBSTR(MAX(sys_connect_by_path(col1,',')),2)
FROM (SELECT col1,
row_number () OVER (ORDER BY col1) as curr,
row_number () OVER (ORDER BY col1)-1 as prev
FROM s1
) vw
CONNECT BY vw.curr = PRIOR prev
SQL> /
SUBSTR(MAX(SYS_CONNECT_BY_PATH(COL1,',')),2)
--------------------------------------------------------------------------------
C,B,A


Here set s1 can be replace with any query data that you want to convert to csv.

To use this as a function/procedure pass the cursor and use its result set as set s1 in the query above. You would need to use a character database type array to collect cursor data

for example:
CREATE OR REPLACE TYPE string_type_array AS TABLE OF VARCHAR2(32767);


CREATE OR REPLACE FUNCTION test_func(p_cur IN OUT sys_refcursor)
RETURN VARCHAR2
AS
l_col_val_tab string_type_array;
l_csv_string VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cur BULK COLLECT INTO l_col_val_tab;
EXIT WHEN p_cur%NOTFOUND;
END LOOP;

WITH s1 AS
(SELECT COLUMN_VALUE as col1
FROM TABLE(l_col_val_tab)
)
SELECT SUBSTR(MAX(sys_connect_by_path(col1,',')),2)
INTO l_csv_string
FROM (SELECT col1,
row_number () OVER (ORDER BY col1) as curr,
row_number () OVER (ORDER BY col1)-1 as prev
FROM s1
) vw
CONNECT BY vw.curr = PRIOR prev;

RETURN l_csv_string;
END test_func;


DECLARE
l_query_cur SYS_REFCURSOR;
BEGIN
OPEN l_query_cur FOR
SELECT 'A' as col1
FROM dual
UNION
SELECT 'B' as col1
FROM dual
UNION
SELECT 'C' as col1
FROM dual;

DBMS_OUTPUT.put_line(test_func(p_cur => l_query_cur));
END;