Oracle Hierarchical Queries

It is rather common developer task to draw a hierarchical data on the screen. You have tons of controls that draw a tree like structures. But did you know that when you are using Oracle you are able to select hierarchical data direct from database? How?
It is rather simple. As an example we will draw a tree of an organization structure. Lets create a simple table with employees:
CREATE TABLE EMP
(
ID NUMBER(2,0) NOT NULL,
REFID NUMBER(2,0) NULL,
NAME VARCHAR2(40 BYTE) NULL
)

Its time to fill the table with data:

INSERT INTO EMP(ID, REFID, NAME)
VALUES(1, 1, 'John');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(2, 1, 'Mary');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(3, 1, 'Tom');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(4, 2, 'Michael');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(5, 5, 'Richard');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(6, 6, 'Andy');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(7, 6, 'Helen');

Guess what? That’s almost done! We have to write only one select statement to get the hierarchical data:
SELECT id, refid, name, LEVEL, SYS_CONNECT_BY_PATH(name,'/') path
FROM emp start WITH id = refid
connect BY nocycle prior id = refid

Voila! You should get something like that:
Peace of cake! Right?
Originally published at Sunday, February 25, 2007