how to return comma seperated values in oracle SP

Question

If I have a simple query like:

OPEN cursor FOR 
SELECT USER_ID FROM USER_TABLE WHERE USER_ID = V_SOME_USER;

this will return records in different rows but how can I return the rows in the following format:

'userid1', 'userid2', 'userid3'.....'useridN'

I want to do this because I want to send this off as a parameter to another stored procedure...Also, what is the limit on how big the string can be when passed to the SP as parameter

What I have so far:

    l_str  varchar2(32767) default null;
    l_sep  varchar2(10) default null;
    l_sep1  varchar2(10) default null;
begin
    for x in ( SELECT USER_ID FROM USER_TABLE WHERE USER_ID = V_SOME_USER ) loop
        l_str := l_str || l_sep || x.b || l_sep;
       l_sep := '''';
       l_sep1 := ''',';
   end loop;

Though this doesnt give expected results

Answer 1

Though you should explore the other ways of doing this. In case you need so, there is a easy way, albeit there is a catch.

Which version of Oracle you are using? Oracle 11gR2 have a nice implementation, LISTAGG.

Like -

SELECT LISTAGG(last_name, ', ') 
    WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares            07-DEC-02

Check out.