Oracle Connect By Clause to Perform Hierarchical Queries.

 If we want to perform hierarchical queries we need to use connect by clause in our sql query.

Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure.

One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table.

But we are not going to take it as you can reference it almost on any site that hosts oracle based content.

We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former.

In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent.

with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME

This confirms that REFRENCED_NAME is the parent and we are going to base this hierarchy on this relation.

Next addendum to connect by clause we are going to make is nocycle attribute. In Oracle there is a possibility of cyclical dependency in this case we would not have clear tree. Oracle throws an error when there is such cyclical data in your query. If you know this is the possibility its better to use nocycle clause. 

So here's the code that would print all the dependencies of table 'ORDER_HEADER_TABLE'.

select CONNECT_BY_ROOT REFERENCED_NAME  ||   sys_connect_by_path( name , '->' ),
d.* from dba_dependencies d
where owner = 'HR' 
connect by nocycle prior NAME= REFERENCED_NAME 
start with REFERENCED_NAME = 'ORDER_HEADER_TABLE' 
order by 1

Demo of DBA_DEPENDENCIES HIERARCHY

SYS_CONNECT_BY_PATH gives you bread crumb like navigation from root node (in this case ORDER_HEADER_TABLE) to its child leaf nodes or intermediate nodes. So basically you have printed the hierarchy and path tracing from parent to child nodes. Hope you found this useful.