René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Flat Hierarchy with Oracle SQL

create global temporary table stack(lvl number, val number) 
on commit delete rows;

create table group_enumeration (empno number(4), mgr_no number(4));

begin

  for r in (
    select 
      level, empno, mgr 
    from 
      emp 
    start with mgr is null 
    connect by prior empno=mgr)
  loop
    delete from stack where lvl >= r.level;

    insert into group_enumeration select
      r.empno,val from stack;

    insert into stack values(r.level,r.empno);

  end loop;
end;
/


select
  empno, mgr_no
from
  group_enumeration
where
  empno = 7369;

See also connect by.