Implementing search feature for the tree region is little tricky. Consider we have tree region created on the employee table EMP as below. If we search for employee JAMES and we want to show JAMES node, then we must show everyone up in the hierarchy till the root node, i.e. KING > BLAKE > JAMES. Luckily, we can easily get hierarchy (traverse path) using sys_connect_by_path . For e.g. consider below query select ename, empno, mgr, sys_connect_by_path(empno, ':' )|| ':' as emp_path from emp start with mgr is null connect by prior empno = mgr order siblings by ename This query gives all employee rows from EMP table, along with path data, from the root node to current node. Now, we can use this path data to filter rows that are required to show any node. For e.g. we can use below query to get all the required nodes to show JAMES node. with emp_tree as ( select ename, empno, mgr, sys_connect_by...
Sharing is a cool way of learning!