Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  strings stringlist,

  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( stringlist() );
    RETURN ODCIConst.Success;
  END;

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

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY column_value )
    INTO   returnValue
    FROM   TABLE( SELF.strings );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.strings := SELF.strings MULTISET UNION ALL ctx2.strings;
    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 value ) AS listagg
FROM   test_data
GROUP BY id;


ID
WM_CONCAT
LISTAGG


1
A,B,C
A,B,C

2
D,E
D,E