How to combine values into Comma Separated string using Oracle function, Convert selected values into comma separated string

This is function to convert values into comma separated string. This function takes input as cursor and joining delimiter and return a string. This code supports oracle 8 and above. One can test for lower versions also.

Please let me know if you face any problem.

Code Snippet

1. CREATE OR REPLACE FUNCTION Fnc_JOIN
2. (
3.     p_cursor sys_refcursor,
4.     p_del VARCHAR2 := ','
5. ) RETURN VARCHAR2
6. IS
7.     l_value   VARCHAR2(32767);
8.     l_result  VARCHAR2(32767);
9. BEGIN
10.     LOOP
11.         FETCH p_cursor INTO l_value;
12.         EXIT WHEN p_cursor%NOTFOUND;
13.         IF l_result IS NOT NULL THEN
14.             l_result := l_result || p_del;
15.         END IF;
16.         l_result := l_result || l_value;
17.     END LOOP;
18.     RETURN l_result;
19. END ;

 

How to call This function:

 

Code Snippet

1. select fnc_join(cursor (SELECT emp_name FROM emp_table)) from dual;

 

One can change the concatenation delimiter to any other character as per requirement.