create table my_item_hierarchy
(
	id_item		number	,
	id_item_sup	number	,
	ordre		number
)
;

insert into my_item_hierarchy values (1, null, 1);
insert into my_item_hierarchy values (2, 1, 2);
insert into my_item_hierarchy values (3, 2, 3);
insert into my_item_hierarchy values (4, 3, 4);
insert into my_item_hierarchy values (5, 3, 5);
insert into my_item_hierarchy values (6, 4, 6);
insert into my_item_hierarchy values (7, 4, 7);
insert into my_item_hierarchy values (8, 2, 8);
insert into my_item_hierarchy values (9, 1, 9);
insert into my_item_hierarchy values (10, 9, 10);

with v00 as
(	
	select 
	id_item
	, id_item_sup
	, level lvl
	, sys_connect_by_path(id_item, '/') my_path
	from my_item_hierarchy
	connect by
	prior id_item = id_item_sup
	start with id_item = 1
	order by level, id_item
), v01 as
(
	select 
	id_item
	, lvl
	, my_path
	, instr(my_path, '/', -1, 1) pos_slash1
	, instr(my_path, '/', -1, 2) pos_slash2
	, instr(my_path, '/', -1, 3) pos_slash3
	, instr(my_path, '/', -1, 4) pos_slash4
	from v00
)
select id_item
, my_path
, substr(my_path
, pos_slash2 + 1
, pos_slash1 - pos_slash2 - 1
) id_item_sup1
-- ---
, substr(my_path
, pos_slash3 + 1
, pos_slash2 - pos_slash3 - 1
) id_item_sup2
-- ---
, substr(my_path
, pos_slash4 + 1
, pos_slash3 - pos_slash4 - 1
) id_item_sup3
from v01
;

select  id_item,
        sys_connect_by_path(id_item,'/') my_path,
        to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+$',1,1,null,1)) id_item_sup1,
        to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+/\d+$',1,1,null,1)) id_item_sup2,
        to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+/\d+/\d+$',1,1,null,1)) id_item_sup3
  from  my_item_hierarchy
  start with id_item_sup is null
  connect by prior id_item = id_item_sup
  order by level,
           id_item
/

   ID_ITEM MY_PATH    ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3
---------- ---------- ------------ ------------ ------------
         1 /1
         2 /1/2                  1
         9 /1/9                  1
         3 /1/2/3                2            1
         8 /1/2/8                2            1
        10 /1/9/10               9            1
         4 /1/2/3/4              3            2            1
         5 /1/2/3/5              3            2            1
         6 /1/2/3/4/6            4            3            2
         7 /1/2/3/4/7            4            3            2

10 rows selected.

SQL>

WITH rsq (  id,      my_path,         pid, ppid, pppid) AS
 (SELECT id_item, '/'||id_item, id_item_sup, NULL,  NULL 
    FROM my_item_hierarchy
   WHERE id_item_sup is null
   UNION ALL
  SELECT i.id_item, my_path||'/'||i.id_item, r.id, r.pid, r.ppid  
    FROM rsq r
    JOIN my_item_hierarchy i
      ON r.id = i.id_item_sup)
SELECT id, my_path, pid, ppid, pppid
  FROM rsq;

ID   MY_PATH		PID	PPID	PPPID
---------------------------------------------
1	/1			
2	/1/2		1		
9	/1/9		1		
3	/1/2/3		2	1	
8	/1/2/8		2	1	
10	/1/9/10		9	1	
4	/1/2/3/4	3	2	1
5	/1/2/3/5	3	2	1
6	/1/2/3/4/6	4	3	2
7	/1/2/3/4/7	4	3	2

with h (id_item, sup, lvl) as
     (
       select  connect_by_root id_item, id_item_sup, level
       from    my_item_hierarchy
       connect by id_item = prior id_item_sup and level <= 3
     )
select id_item, id_item_sup1, id_item_sup2, id_item_sup3
from   h
pivot  (max(sup) for lvl in (1 as id_item_sup1, 2 as id_item_sup2, 3 as id_item_sup3))
;

   ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3
---------- ------------ ------------ ------------
         1                                       
         6            4            3            2
         2            1                          
         4            3            2            1
         5            3            2            1
         8            2            1             
         3            2            1             
         7            4            3            2
         9            1                          
        10            9            1             

create table my_node
(
	id_node 		number	,	
	node_type	 	number	
)
;

insert into my_node values (1, 100);
insert into my_node values (2, null);
insert into my_node values (3, 200);
insert into my_node values (4, null);
insert into my_node values (5, 300);
insert into my_node values (6, 400);
insert into my_node values (7, 500);

insert into my_node values (8, null);
insert into my_node values (9, null);
insert into my_node values (10, null);

insert into my_node values (11, null);
insert into my_node values (12, null);
insert into my_node values (13, null);

insert into my_node values (14, null);
insert into my_node values (15, null);
insert into my_node values (16, null);

create table my_node_hierarchy
(
	id_node 		number	,	
	id_node_sup 	number	
)
;

insert into my_node_hierarchy values (1, null);
insert into my_node_hierarchy values (2, 1);
insert into my_node_hierarchy values (3, 2);
insert into my_node_hierarchy values (4, 3);

insert into my_node_hierarchy values (5, 4);
insert into my_node_hierarchy values (6, 4);
insert into my_node_hierarchy values (7, 4);

insert into my_node_hierarchy values (14, 5);
insert into my_node_hierarchy values (15, 5);
insert into my_node_hierarchy values (16, 5);

insert into my_node_hierarchy values (11, 6);
insert into my_node_hierarchy values (12, 6);
insert into my_node_hierarchy values (13, 6);

insert into my_node_hierarchy values (8, 7);
insert into my_node_hierarchy values (9, 7);
insert into my_node_hierarchy values (10, 7);

column path format a15
column n1 format 99
column n2 format 99
column n3 format 99
select *
from
(
	with t1(id_node, id_node_sup, node_type, lvl, path) as
	(
		select nh.id_node, nh.id_node_sup
		, my_node.node_type
		, 1 lvl
		, '/' || nh.id_node path
		from my_node_hierarchy nh, my_node
		where 1 = 1
		and nh.id_node = my_node.id_node
		and id_node_sup is null
		union all
		select t2.id_node, t2.id_node_sup
		, my_node.node_type
		, lvl + 1
		, t1.path || '/' || t2.id_node
		from my_node_hierarchy t2, t1, my_node
		where 1 = 1
		and t2.id_node = my_node.id_node
		and t2.id_node_sup = t1.id_node
	)
	search 
	depth 
	first by lvl, id_node_sup set order1
	cycle id_node set cycle to 1 default 0
	select 
	id_node
	, id_node_sup
	, path
	from t1
	order by order1
)
;

   ID_NODE ID_NODE_SUP PATH             N1  N2  N3
---------- ----------- --------------- --- --- ---
         1             /1                         
         2           1 /1/2              1         
         3           2 /1/2/3            1         
         4           3 /1/2/3/4          3   1    
         5           4 /1/2/3/4/5        3   1    
        14           5 /1/2/3/4/5/14     5   3   1
        15           5 /1/2/3/4/5/15     5   3   1
        16           5 /1/2/3/4/5/16     5   3   1
         6           4 /1/2/3/4/6        3   1    
        11           6 /1/2/3/4/6/11     6   3   1
        12           6 /1/2/3/4/6/12     6   3   1
        13           6 /1/2/3/4/6/13     6   3   1
         7           4 /1/2/3/4/7        3   1    
         8           7 /1/2/3/4/7/8      7   3   1
         9           7 /1/2/3/4/7/9      7   3   1
        10           7 /1/2/3/4/7/10     7   3   1