By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with persons(id, code, parent_id) as (
select 5953, 'COMPANY', null from dual union all
select 230928, 'D', 5953 from dual union all
select 7246, 'C', 230928 from dual union all
select 243928, 'C.5', 7246 from dual union all
select 240961, 'C.3', 7246 from dual union all
select 7287, 'C.4', 7246 from dual union all
select 7286, 'C.2', 7246 from dual union all
select 7285, 'C.1', 7246 from dual
)
select
p.id children_id,
p.code children_code,
p.parent_id,
pp.code parent_code,
prior pp.code as grandparent_code
from persons p
left join persons pp
on p.parent_id = pp.id
start with p.parent_id is null
connect by prior p.id = p.parent_id
| CHILDREN_ID | CHILDREN_CODE | PARENT_ID | PARENT_CODE | GRANDPARENT_CODE |
|---|---|---|---|---|
| 5953 | COMPANY | null | null | null |
| 230928 | D | 5953 | COMPANY | null |
| 7246 | C | 230928 | D | COMPANY |
| 7285 | C.1 | 7246 | C | D |
| 7286 | C.2 | 7246 | C | D |
| 7287 | C.4 | 7246 | C | D |
| 240961 | C.3 | 7246 | C | D |
| 243928 | C.5 | 7246 | C | D |