Query to get navigation for a component

Query to get navigation for a component

WITH portal_registry AS

(SELECT RTRIM(REVERSE(sys_connect_by_path(REVERSE(portal_label), ' >> ')), ' >> ') path,

LEVEL lvl

FROM psprsmdefn

WHERE portal_name = 'EMPLOYEE' 

START WITH PORTAL_OBJNAME = (SELECT PORTAL_OBJNAME FROM psprsmdefn WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_URI_SEG2 = :1) -- Component Name

CONNECT BY PRIOR portal_prntobjname = portal_objname)

SELECT path

FROM portal_registry

WHERE lvl =

(SELECT MAX(lvl)

FROM portal_registry);