jump to navigation

Portal Navigation December 9, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
trackback

I would imagine many of you are familiar with the PSPRSMDEFN table as it is tremendously useful. I would gamble most have a SQL snippet tucked away somewhere that will query it to find the PIA navigation to any given component. However Jim Marion has posted some SQL on his blog that is the tidiest I’ve seen to date (it’s Oracle specific BTW).

Most SQL snippets use multiple joins to the table, I’ve seen one before that used connect_by_prior, but this one is the smallest script yet.

Thanks Jim!

Edit:

this slightly more succinct version may be even better.
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         (portal_label), ' > ')), ' > ') path
  from psprsmdefn
 where portal_name = 'EMPLOYEE'
   and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
 start with portal_uri_seg2 = :1
connect by prior
         portal_prntobjname = portal_objname

Advertisement

Comments

1. C - August 7, 2012

very neat!

2. Jaydip Dewanji - October 3, 2012

Truely Useful! Now I believe this would work in versions >= 8.8 right? What about releases like 8.3? Would they be having columns like PORTAL_URI_SEG2?


Sorry comments are closed for this entry

%d bloggers like this: