Displaying full path of hierarchical data structure

Written by Mandy. Posted in Useful Functions.

I have some data on one table that is heirerchical, i.e. there is a PARENT column that stores a number which is the unique ID of another record in the same table.

There is more than two levels, and for each record I need to display the full path - not just the immediate parent.

Every time I need to do this, I have to trawl through blogs/forums to get the syntax right.


I am creating a functionality within an application for employees to add their skills & expertise to their resource profile.

I am restricting their choices to a list of predefined data in a table called RES_LOV_SKILLS

expertise table


In the front end of the application, I have a page which allows admins to manage this list. When they add a new skill, I want them to be able to choose a parent, and when doing so, enable them to see the full path of the skill ( will see what I mean in a moment).

For more info see Oracle Documentation:

Using this query I was able to print the full category of each skill/expertise:

substr(sys_connect_by_path(expertise,' > '), 4) as d,
id r
from res_lov_skills
start with expertise_parent IS NULL
connect by prior id = expertise_parent;

The results being:

expertise query result


Now I just create an LOV based on these values (the full_path being the display value, and returning the ID as the return value) and I can use it for my users to choose a parent for their new skill and keep them nicely categorised!

expertise add screen



About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2