Portal SQL

Retrieve a Portal Hierarchy using the portal object name

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 = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME)
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (SELECT MAX(LVL)
       FROM PORTAL_REGISTRY)

Retrieve a Portal Hierarchy using the Menu, Component & Market

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_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = :1
AND PORTAL_URI_SEG2 = :2
AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME)
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (SELECT MAX(LVL)
       FROM PORTAL_REGISTRY )