Portal Navigation Path - Oracle PL/SQL Function

Thanks to Jake Gowens a.k.a. Zapp Brannigan

FUNCTION navString (prtlName IN CHAR, prtlObjName IN CHAR) RETURN VARCHAR2 IS

  prtlLabel         VARCHAR2(30);
  prtlParent        VARCHAR2(30);
  result            VARCHAR2(255);

BEGIN

  prtlLabel := '';

  SELECT MAX(PORTAL_LABEL), MAX(PORTAL_PRNTOBJNAME)
    INTO prtlLabel, prtlParent
    FROM PSPRSMDEFN
   WHERE PORTAL_NAME = prtlName
     AND PORTAL_OBJNAME = prtlObjName
     AND PORTAL_REFTYPE = 'C';

  if prtlLabel IS NOT NULL then
    if prtlParent = 'PORTAL_ROOT_OBJECT' then
      result := prtlLabel;
    else
      result := navStringF(prtlName, prtlParent) || ' > ' || prtlLabel;
    end if;
  end if;

  return result;

END navString;

Example:
Select navString(a.portal_name, a.portal_objname)
From psprsmdefn a
Where portal_uri_seg2 = 'JOB_DATA';

Returns:
Workforce Administration > Job Information > Job Data