Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := SUBSTR( SELF.g_string, 2 );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.g_string := SELF.g_string || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
CREATE TABLE test_data ( id, value ) AS
  SELECT 1, 'C' FROM DUAL UNION ALL
  SELECT 1, 'A' FROM DUAL UNION ALL
  SELECT 1, 'B' FROM DUAL UNION ALL
  SELECT 2, 'D' FROM DUAL UNION ALL
  SELECT 2, 'E' FROM DUAL;
5 rows affected

SELECT id,
       wm_concat( value ) AS wm_concat,
       LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS listagg
FROM   test_data
GROUP BY id;


ID
WM_CONCAT
LISTAGG


1
C,B,A
C,A,B

2
D,E
D,E