-- The query below shows a parent - child 
-- (can be manager - employee) hierarchy 
-- setup.


SELECT
 level idx,
 CAST('E-'||DECODE(level, 1, 0,
   DECODE(GREATEST(level,3),3,1,level-1))
     AS VARCHAR2(10)) emp_cd,
 DECODE(level, 1, NULL,
   DECODE(GREATEST(level,3),3,1,level-1))
    parent_idx
FROM DUAL
CONNECT BY level < 7
;

-- The query below uses '-' in the 
-- SYS_CONNECT_BY_PATH, which is part of 
-- Emp_cd, causing the query to throw
-- ORA-30004 error.


WITH q_emp AS (
SELECT
 level idx,
 CAST('E-'||DECODE(level, 1, 0,
   DECODE(GREATEST(level,3),3,1,level-1))
     AS VARCHAR2(10)) emp_cd,
 DECODE(level, 1, NULL,
   DECODE(GREATEST(level,3),3,1,level-1))
     parent_idx
FROM DUAL
CONNECT BY level < 7)
SELECT
 CAST(parent_idx AS VARCHAR2(12)) parent_idx,
 CAST(SUBSTR(sys_connect_by_path(emp_cd,'-'),4)
      AS VARCHAR2(35)) hierarchy_path
FROM q_emp
START WITH parent_idx IS NULL
CONNECT BY NOCYCLE PRIOR idx = parent_idx
ORDER SIBLINGS BY idx;

-- By using a seperator other than "-" 
-- such as " <- ", the ORA-30004 error is 
-- avoided in displaying hierarchy.  


WITH q_emp AS (
SELECT
 level idx,
 CAST('E-'||DECODE(level, 1, 0,
   DECODE(GREATEST(level,3),3,1,level-1))
     AS VARCHAR2(10)) emp_cd,
 DECODE(level, 1, NULL,
   DECODE(GREATEST(level,3),3,1,level-1))
    parent_idx
FROM DUAL
CONNECT BY level < 7
)
SELECT
 CAST(parent_idx AS VARCHAR2(12)) parent_idx,
 CAST(SUBSTR(sys_connect_by_path(emp_cd,' <- ')
      ,4) AS VARCHAR2(35)) hierarchy_path
FROM q_emp
START WITH parent_idx IS NULL
CONNECT BY NOCYCLE PRIOR idx = parent_idx
ORDER SIBLINGS BY idx;