PROMPT Object creation
CREATE TABLE mhe_foo(id NUMBER, name  VARCHAR2(1), parent NUMBER)
/

INSERT INTO mhe_foo VALUES(1, 'a', 0)
/
INSERT INTO mhe_foo VALUES(2, 'b', 1)
/
INSERT INTO mhe_foo VALUES(3, 'c', 1)
/
INSERT INTO mhe_foo VALUES(4, 'd', 2)
/
INSERT INTO mhe_foo VALUES(5, 'e', 3)
/

CREATE OR REPLACE TYPE concat_expr AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
  MAP MEMBER FUNCTION f_map RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY concat_expr AS
  MAP MEMBER FUNCTION f_map
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN str;
  END f_map;
END;
/

CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),

  STATIC FUNCTION odciaggregateinitialize (
	 sctx IN OUT concat_all_ot)
	 RETURN NUMBER,

  MEMBER FUNCTION odciaggregateiterate (
	 SELF IN OUT concat_all_ot,
	 ctx IN concat_expr)
	 RETURN NUMBER,

  MEMBER FUNCTION odciaggregateterminate (
	 SELF IN concat_all_ot,
	 returnvalue OUT VARCHAR2,
	 flags IN NUMBER)
	 RETURN NUMBER,

  MEMBER FUNCTION odciaggregatemerge (
	 SELF IN OUT concat_all_ot,
	 ctx2 concat_all_ot)
	 RETURN NUMBER);
/



CREATE OR REPLACE TYPE BODY concat_all_ot
AS
  STATIC FUNCTION odciaggregateinitialize (
	 sctx IN OUT concat_all_ot)
	 RETURN NUMBER
  IS
  BEGIN
	 sctx := concat_all_ot (NULL, NULL);
	 RETURN odciconst.success;
  END;
  MEMBER FUNCTION odciaggregateiterate (
	 SELF IN OUT concat_all_ot,
	 ctx IN concat_expr)
	 RETURN NUMBER
  IS
  BEGIN
	 IF SELF.str IS NOT NULL THEN
	   SELF.str := SELF.str || ctx.del;
	 END IF;
	 SELF.str := SELF.str || ctx.str;
	 RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateterminate (
	 SELF IN concat_all_ot,
	 returnvalue OUT VARCHAR2,
	 flags IN NUMBER)
	 RETURN NUMBER
  IS
  BEGIN
	 returnvalue := SELF.str;
	 RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregatemerge (
	 SELF IN OUT concat_all_ot,
	 ctx2 IN concat_all_ot)
	 RETURN NUMBER
  IS
  BEGIN
	 IF SELF.str IS NOT NULL THEN
	   SELF.str := SELF.str || SELF.del;
	 END IF;
	 SELF.str := SELF.str || ctx2.str;
	 RETURN odciconst.success;
  END;
END;
/

CREATE OR REPLACE FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
/



PROMPT Source data
SELECT *
  FROM mhe_foo
/

PROMPT output using CONCAT_ALL
COLUMN names FORMAT A30
select id
     , concat_all(concat_expr(name,'-')) over (order by level desc) names
  from mhe_foo
connect by prior parent = id
start with id = 4
/

DROP FUNCTION concat_all
/
DROP TYPE concat_all_ot
/
DROP TYPE concat_expr
/
DROP TABLE mhe_foo
/

SQL> @C:\useful\orafaq
Object creation

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Type created.


Type body created.


Type created.


Type body created.


Function created.

Source data

        ID N     PARENT
---------- - ----------
         1 a          0
         2 b          1
         3 c          1
         4 d          2
         5 e          3

output using CONCAT_ALL

        ID NAMES
---------- ------------------------------
         1 a
         2 a-b
         4 a-b-d


Function dropped.


Type dropped.


Type dropped.


Table dropped.

SQL>