CREATE OR REPLACE TYPE "STRING_AGG_TYPE" AS OBJECT(
total VARCHAR2(4000),
STATIC FUNCTION odciaggregateinitialize(sc
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(sc
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;
/
CREATE OR REPLACE FUNCTION stragg(input VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
USING string_agg_type;
stragg is also from Tom Kyte. His new method of using hierarchical queries is interesting but more expensive than using his original method of stragg.
Main Topics
Browse All Topics
by: angelIIIPosted on 2007-11-23 at 13:17:05ID: 20340704
there is a "easy" way to do it, shown/explained here:
http://halisway.blogspot.c
if you need help implementing it, please ask