I have just merged your queries into one and by replacing :1 bind variable with component name in following query and running user can find the navigation of the component (Thanks for your article, it was of great help):

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)
CONNECT BY PRIOR portal_prntobjname = portal_objname)
SELECT path
FROM portal_registry
WHERE lvl =
(SELECT MAX(lvl)
FROM portal_registry);

Now user has to run only one query...:)

Thanks Kritdeo Bais, appreciate that!

Hi,

Here is an another similar way to find the path of your component.

SELECT LPAD('--',2*(LEVEL-1)) || PORTAL_LABEL "NAVIGATION"
FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A
WHERE PORTAL_NAME = 'EMPLOYEE' ) B
WHERE B.PORTAL_PRNTOBJNAME != ' '
START WITH (B.PORTAL_URI_SEG2 IN 'Component name in caps')
CONNECT BY PRIOR B.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME;

Works like a dream. Thanks.

this is awesome

There's also this

--
-- SHOW THE PORTAL NAVIGATION MENU USED TO ACCESS A COMPONENT
--
SELECT DISTINCT
CASE WHEN G.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(G.PORTAL_LABEL)+'>' END
+CASE WHEN F.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(F.PORTAL_LABEL)+'>' END
+CASE WHEN E.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(E.PORTAL_LABEL)+'>' END
+CASE WHEN D.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(D.PORTAL_LABEL)+'>' END
+CASE WHEN C.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(C.PORTAL_LABEL)+'>' END
+CASE WHEN B.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(B.PORTAL_LABEL)+'>' END
+CASE WHEN A.PORTAL_LABEL IS NULL THEN '' ELSE RTRIM(A.PORTAL_LABEL) END
FROM PSPRSMDEFN A
LEFT JOIN PSPRSMDEFN B ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN C ON B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN D ON C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN E ON D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN F ON E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN G ON F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME
WHERE A.PORTAL_URI_SEG2 = ''

That shows you the navigation to the component

This is cool that we are able to get the personal loans moreover, that opens up completely new possibilities.

Hello, Peter. After much searching, your SQL is the best I've found for my SQL Server environment. All of the other information for this need is Oracle specific. Thank you!

I'm not familiar with this table and have a question . . . The results of this SQL shows the navigation for all components. What do I include in the select to show the menu, component, and / or page names so I can find the navigation for a specific component? Alternatively, how do get the navigation for one specific component or page?

Thanks for contributing this information!
Pete