Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dept(
   DEPT_ID     INTEGER  NOT NULL PRIMARY KEY 
  ,DEPT_NAME   VARCHAR(15) NOT NULL
  ,DEPT_PARENT INTEGER 
);
begin
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5500,'World',NULL);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5510,'Region 1',5500);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5511,'Cell 1 Region 1',5510);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5512,'Cell 2 Region 1',5510);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5513,'Cell 3 Region 1',5510);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5514,'Cell 4 Region 1',5510);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5515,'Cell 5 Region 1',5510);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5520,'Region 2',5500);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5521,'Cell 1 Region 2',5520);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5522,'Cell 2 Region 2',5520);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5530,'Region 3',5500);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5531,'Cell 1 Region 3',5530);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5532,'Cell 2 Region 3',5530);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5540,'Region 4',5500);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5533,'Cell 1 Region 4',5540);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5534,'Cell 2 Region 4',5533);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5590,'Region 5',5500);
INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5591,'Cell 1 Region 5',5590);
end;
/
1 rows affected

with cte(node_id, dept_id, dept_parent, lvl) as (
	select dept_id node_id, dept_id, dept_parent, 0 lvl from dept
	union all 
	select c.node_id, d.dept_id, d.dept_parent, c.lvl + 1
	from cte c
	inner join dept d on d.dept_id = c.dept_parent
)
select dept_id ancestor, node_id node, lvl 
from cte 
where lvl > 0
order by node, ancestor


ANCESTOR
NODE
LVL


5500
5510
1

5500
5511
2

5510
5511
1

5500
5512
2

5510
5512
1

5500
5513
2

5510
5513
1

5500
5514
2

5510
5514
1

5500
5515
2

5510
5515
1

5500
5520
1

5500
5521
2

5520
5521
1

5500
5522
2

5520
5522
1

5500
5530
1

5500
5531
2

5530
5531
1

5500
5532
2

5530
5532
1

5500
5533
2

5540
5533
1

5500
5534
3

5533
5534
1

5540
5534
2

5500
5540
1

5500
5590
1

5500
5591
2

5590
5591
1

ANCESTOR	NODE	LVL
5500	5510	1
5500	5511	2
5510	5511	1
5500	5512	2
5510	5512	1
5500	5513	2
5510	5513	1
5500	5514	2
5510	5514	1
5500	5515	2
5510	5515	1
5500	5520	1
5500	5521	2
5520	5521	1
5500	5522	2
5520	5522	1
5500	5530	1
5500	5531	2
5530	5531	1
5500	5532	2
5530	5532	1
5500	5533	2
5540	5533	1
5500	5534	3
5533	5534	1
5540	5534	2
5500	5540	1
5500	5590	1
5500	5591	2
5590	5591	1