create table s_composition ( id number, composition_parent_id number, full_name varchar2(100));

insert into s_composition values( 1, null, 'name');

SELECT level, id, composition_parent_id, full_name
FROM s_composition
START WITH composition_parent_id is null
CONNECT BY PRIOR id=composition_parent_id;
     LEVEL         ID COMPOSITION_PARENT_ID FULL_NAME
---------- ---------- --------------------- --------------------------------------------------------------------------------
         1          1                       name