Sometimes for front-end applications it’s useful to display a list of items to a user, but those items are stored as multiple records in a database table. This function takes a single field in a cursor and joins the values together into a delimited string.

FUNCTION join_records(p_key_name  IN VARCHAR2
                     ,p_key_val   IN VARCHAR2
                     ,p_column    IN VARCHAR2
                     ,p_table     IN VARCHAR2
                     ,p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 AS
  v_result  VARCHAR2(4000);
  v_sep     VARCHAR2(1);
  v_val     VARCHAR2(4000);
  refcursor SYS_REFCURSOR;
BEGIN

  OPEN refcursor FOR 'SELECT ' || p_column || '
                      FROM ' || p_table || '
                      WHERE ' || p_key_name || ' = :x '
    USING p_key_val;

  LOOP
    FETCH refcursor
      INTO v_val;
    EXIT WHEN refcursor%NOTFOUND;
    v_result := v_result || v_sep || v_val;
    v_sep    := p_delimiter;
  END LOOP;
  CLOSE refcursor;

  RETURN v_result;
END;

Usage

your_package.join_records('pk_col', '1', 'my_col', 'my_table')

UPDATE : Stragg Function

A much better way of doing this is described on Ask Tom. This is MUCH faster - the column is kind of hard to read so I’ve duplicated the necessary code here - note that I had no contribution whatsoever with this code.

CREATE OR REPLACE FUNCTION stragg(input varchar2 )
 RETURN varchar2
 PARALLEL_ENABLE AGGREGATE USING string_agg_type;

CREATE OR REPLACE type string_agg_type as object
  (
    total varchar2(4000),

    static function
         ODCIAggregateInitialize(sctx IN OUT string_agg_type )
         return number,

    member function
         ODCIAggregateIterate(self IN OUT string_agg_type ,
                              value IN varchar2 )
         return number,

    member function
         ODCIAggregateTerminate(self IN string_agg_type,
                                returnValue OUT  varchar2,
                                flags IN number)
         return number,

    member function
         ODCIAggregateMerge(self IN OUT string_agg_type,
                            ctx2 IN string_agg_type)
         return number
 );


CREATE OR REPLACE type body string_agg_type
  is
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  return number
  is
  begin
      sctx := string_agg_type( null );
      return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                       value IN varchar2 )
  return number
  is
  begin
      self.total := self.total || ',' || value;
      return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN string_agg_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
  return number
  is
  begin
      returnValue := ltrim(self.total,',');
      return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                     ctx2 IN string_agg_type)
  return number
  is
  begin
      self.total := self.total || ctx2.total;
      return ODCIConst.Success;
  end;
end;

Usage

SELECT stragg(your_column) FROM your_table WHERE rownum < 10;