Blog Pro de Jean-Baptiste HEREN

Just replace the 'MY_COMPONENT_GBL' parameter in the following sql script. This works for MS SQl Server. You can add more levels by adding Outer Joins.

[SQL]
/* Where is my component in the portal */
SELECT
A.PORTAL_NAME,
E.PORTAL_LABEL AS Parent4_folder,
D.PORTAL_LABEL AS Parent3_folder,
C.PORTAL_LABEL AS Parent2_folder,
B.PORTAL_LABEL AS Parent_folder,
A.PORTAL_LABEL AS Component
FROM
PSPRSMDEFN A
LEFT JOIN PSPRSMDEFN B
ON B.PORTAL_NAME = A.PORTAL_NAME
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN C
ON C.PORTAL_NAME = B.PORTAL_NAME
AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN D
ON D.PORTAL_NAME = C.PORTAL_NAME
AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN E
ON E.PORTAL_NAME = D.PORTAL_NAME
AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
WHERE A.PORTAL_REFTYPE = 'C'
-- Parameter to set :
  AND A.PORTAL_URI_SEG2 = 'MY_COMPONENT_GBL'

Thanks to François R. for the following code which will do the same recursively. (Oracle only)

[SQL]
SELECT DISTINCT RTRIM(REVERSE
(SYS_CONNECT_BY_PATH(REVERSE
((SELECT B.PORTAL_LABEL FROM PSPRSMDEFNLANG B 
WHERE A.PORTAL_NAME = B.PORTAL_NAME
AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME
AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE
AND B.LANGUAGE_CD = 'FRA')), ' > ')), ' > ') CHEMIN
FROM PSPRSMDEFN A
WHERE A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
START WITH PORTAL_URI_SEG2 = 'MY_COMPONENT'
CONNECT BY PRIOR A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME;