GROUP_CONCAT

CREATE OR REPLACE TYPE agg_t AS OBJECT (

str_agg VARCHAR2(4000),

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT agg_t)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT agg_t,
value IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(self IN agg_t,
return_value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT agg_t,
ctx2 IN agg_t) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY agg_t IS

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT agg_t)
RETURN NUMBER IS
BEGIN
sctx := agg_t(null);
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT agg_t, value IN VARCHAR2)
RETURN NUMBER IS
BEGIN
str_agg := str_agg || value;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN agg_t,
return_value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
BEGIN
return_value := str_agg;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT agg_t,
ctx2 IN agg_t) RETURN NUMBER IS
BEGIN
str_agg := str_agg || ctx2.str_agg;
RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION GroupConcat (input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE aggregate USING agg_t;
/