PL/SQL: Joinrows - Convert multiple [returned cursor] rows into one row

This routine converts multiple rows into a single row. The trick is to pass the data via a SYS_REFCURSOR (see the CURSOR call in the example). Example Usage :

select joinrows(cursor(select contract_id from contract where rownum < 10 )) from dual;

CREATE OR REPLACE FUNCTION joinrows(
 sysref_cursor_in      sys_refcursor,
 s_delimiter_in        VARCHAR2 := ',' )
RETURN VARCHAR2
IS
   s_value            VARCHAR2(32767) := Null;
   s_result           VARCHAR2(32767) := Null;
   s_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN

   LOOP
      FETCH sysref_cursor_in
         INTO s_value;

      EXIT WHEN sysref_cursor_in%NOTFOUND;

      IF (s_result IS NOT NULL) THEN
       s_result := s_result||s_delimiter;
      END IF;

      s_result := s_result||s_value;

   END LOOP;

   RETURN s_result;

END joinrows;

Results:

0,2,3,9,37,404,818,991,1178