PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
Oracle Tree Walking
A handy feature in Oracle is the tree walking feature. For those people who have not come across this handy feature it allows you to recursively join a table back on to itself to retrieve all records in a hierarchy.

For example you can retrieve all employees who report to a given person and all the employees who report to those people and so on until you have all the people.

To achieve this you need to make use of the CONNECT BY command. In the following example we select the employee name and then specify the value in the tree where we want to begin. To do this we use the START WITH command to identify where to begin, then we issue the CONNECT BY PRIOR which recursively joins all employee records where the emp_reports_to_emp_id is equal to the previous emp_id.

SELECT emp_name
	FROM employee
	START WITH emp_id = 270
	CONNECT BY PRIOR emp_id = emp_reports_to_emp_id;

This is a great feature and can be a real timesaver.

 

Top of Page

Legal Notice

Ken Howe 2011