The hierarchical query shown below, works without error when employee and manager are not the same (the row five in the query table employee and manager having id of 102 in the example below). If the employee has no manager (organization head/CEO), it has to be typically setup as a NULL value for the query syntax to work. 

SELECT q_tab.emp_no, q_tab.Mgr_No, 
  SUBSTR(SYS_CONNECT_BY_PATH(q_tab.emp_no, ' <-- '),6) management_hierarchy, 
  CONNECT_BY_ISLEAF is_leaf,  LEVEL 
FROM  (SELECT  1 no, 1001 emp_no, 102  mgr_no FROM DUAL UNION 
       SELECT  2 no, 1002 emp_no, 102  mgr_no FROM DUAL UNION 
       SELECT  3 no, 1003 emp_no, 102  mgr_no FROM DUAL UNION 
       SELECT  4 no, 2001 emp_no, 1001  mgr_no FROM DUAL UNION 
       SELECT  5 No, 102  Emp_No, 102  Mgr_No From Dual
      ) q_tab
START WITH Q_Tab.Emp_No = &i_Emp 
CONNECT BY PRIOR Q_Tab.Emp_No = Q_Tab.Mgr_No 
ORDER SIBLINGS BY q_tab.emp_no; 

-- Input of Emp_No = 102 will cause error ORA-01436


SELECT q_tab.emp_no, q_tab.Mgr_No, 
  SUBSTR(SYS_CONNECT_BY_PATH(q_tab.emp_no, ' <-- '),6) management_hierarchy, 
  CONNECT_BY_ISLEAF is_leaf,   LEVEL 
FROM   (SELECT  1 no, 1001 emp_no, 102   mgr_no FROM DUAL UNION 
        SELECT  2 no, 1002 emp_no, 102   mgr_no FROM DUAL UNION 
        SELECT  3 no, 1003 emp_no, 102   mgr_no FROM DUAL UNION 
        SELECT  4 no, 2001 emp_no, 1001  mgr_no FROM DUAL UNION 
        SELECT  5 No, 102  Emp_No, NULL  Mgr_No From Dual
       ) q_tab
START WITH Q_Tab.Emp_No = &i_Emp 
CONNECT BY PRIOR Q_Tab.Emp_No = Q_Tab.Mgr_No 
ORDER SIBLINGS BY q_tab.emp_no; 

Using NULL value for manager_id -> Query Output

The hierarchical query shown below, works without error when employee and manager are the same by the use of CONNECT BY NOCYCLE PRIOR syntax. The CONNECT_BY_ISCYCLE evalues to 1 if a cycle exists.
Note: The NOCYCLE is reqired for using CONNECT_BY_ISCYCLE. 

-- CONNECT BY NOCYCLE PRIOR (will resolve ORA-01436 error)
-- CONNECT_BY_ISCYCLE shows the existence of cycle (manager/employee being the same)

SELECT q_tab.emp_no, q_tab.Mgr_No, 
  SUBSTR(SYS_CONNECT_BY_PATH(q_tab.emp_no, ' <-- '),6) management_hierarchy, 
  CONNECT_BY_ISLEAF is_leaf, 
  CONNECT_BY_ISCYCLE is_cycle,
  LEVEL 
FROM 
  (SELECT  1 no, 1001 emp_no, 102   mgr_no FROM DUAL UNION 
   SELECT  2 no, 1002 emp_no, 102   mgr_no FROM DUAL UNION 
   SELECT  3 no, 1003 emp_no, 102   mgr_no FROM DUAL UNION 
   SELECT  4 no, 2001 emp_no, 1001  mgr_no FROM DUAL UNION 
   Select  5 No, 102  Emp_No, 102   Mgr_No From Dual
) q_tab
START WITH Q_Tab.Emp_No = &i_Emp 
CONNECT BY NOCYCLE PRIOR Q_Tab.Emp_No = Q_Tab.Mgr_No 
ORDER SIBLINGS BY q_tab.emp_no;

Resolve Oracle Error ORA-01436

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 08, 2013

  2338