Oracle 10g : Connect by prior

Database : Oracle 10g
Schema : Scott/Tiger

If you have a hiearchial table, the following generic query presents the data in a hierarchical form.

column graph format a10;

select rpad( '*', 2*level, '*' ) graph, level, t.*
from t
connect by prior p_node = c_node;

p_node = parent node
c_node = child node

If you want to get the hierarchy of a particular node, use "starts with".

Eg :

column graph format a10
select rpad( '*', 2*level, '*' ) graph, level, t.*
from t
start with c_node = '&node_number'
connect by prior p_node = c_node;

Let's take a look at the standard emp table on scott/tiger schema.

To see the full hierarchy in the table (employee -> Manager relationship), fire the following query :

select rpad( '*', 2*level, '*' ) graph, level, e.*
from emp e
connect by prior e.mgr = e.empno
order by deptno;

Let's start with an employee in a department and see what happens.

select rpad( '*', 2*level, '*' ) graph, level, e.*
from emp e
start with e.empno = 7934
connect by prior e.mgr = e.empno
order by deptno;

Long live "Tom kyte".

Good Luck,
r-a-v-i