Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE OBJECT
             (ID NUMBER(38),
              NAME VARCHAR2(8));
INSERT INTO OBJECT
            (ID,
             NAME)
            VALUES (1,
                    'FOO');
1 rows affected

INSERT INTO OBJECT
            (ID,
             NAME)
            VALUES (2,
                    'BAR');
1 rows affected

INSERT INTO OBJECT
            (ID,
             NAME)
            VALUES (3,
                    'FOOBAR');
1 rows affected

CREATE TABLE HIERARCHY
             (ID NUMBER(38),
              PATH VARCHAR2(8));
INSERT INTO HIERARCHY
            (ID,
             PATH)
            VALUES (1,
                    '1/2/3');
1 rows affected

INSERT INTO HIERARCHY
            (ID,
             PATH)
            VALUES (2,
                    '3/2/1');
1 rows affected

INSERT INTO HIERARCHY
            (ID,
             PATH)
            VALUES (3,
                    '1/3');
1 rows affected

INSERT INTO HIERARCHY
            (ID,
             PATH)
            VALUES (4,
                    '2');
1 rows affected

INSERT INTO HIERARCHY
            (ID,
             PATH)
            VALUES (5,
                    '');
1 rows affected

WITH CTE(I)
AS
(
SELECT 1 I
       FROM DUAL
UNION ALL
SELECT CTE.I + 1 I
       FROM CTE
       WHERE CTE.I <= (SELECT MAX(REGEXP_COUNT(HIERARCHY.PATH, '/')) + 1
                              FROM HIERARCHY)
)
SELECT HIERARCHY.ID HIERARCHY_ID,
       HIERARCHY.PATH HIERARCHY_PATH,
       LISTAGG(OBJECT.NAME, '/') WITHIN GROUP (ORDER BY CTE.I) OBJECT_PATH
       FROM HIERARCHY
            LEFT JOIN CTE
                      ON CTE.I <= REGEXP_COUNT(HIERARCHY.PATH, '/') + 1
            LEFT JOIN OBJECT
                      ON OBJECT.ID = TO_NUMBER(REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/'))
       GROUP BY HIERARCHY.ID,
                HIERARCHY.PATH
       ORDER BY HIERARCHY.ID;
       


HIERARCHY_ID
HIERARCHY_PATH
OBJECT_PATH


1
1/2/3
FOO/BAR/FOOBAR

2
3/2/1
FOOBAR/BAR/FOO

3
1/3
FOO/FOOBAR

4
2
BAR

5
null
null
HIERARCHY_ID	HIERARCHY_PATH	OBJECT_PATH
1	1/2/3	FOO/BAR/FOOBAR
2	3/2/1	FOOBAR/BAR/FOO
3	1/3	FOO/FOOBAR
4	2	BAR
5	null	null