Oracle Portal content report

Seems like a big hole in the portal product is a way to determine a catalog of what you have as far as pages go. Also a major problem is knowing what pages are using what template and what pages are using which pageskin.

This makes it difficult to determine what you might break when modifying a template or modifying a pageskin.

After much pain an suffering while digging through the portal schema I came up with a query that gives me a pretty good report which has this information.

Since I am not an employee of Oracle I am not privy to documentation on the schema so my interpretation of the columns are just my best guess.

--Get the pages with no template or page skin
select distinct
    'Page' as object_type,
    pageSite.name as page_group,
    page.name as name,
    portal.wwpob_page_util.get_page_url(page.id,page.siteid) as url,
    null as template_name,
    null as template_tab,
    null as pageskin_name
from portal.wwpob_page$ page,
    portal.wwsbr_sites$ pageSite
where page.template_id is null
 and page.ui_template_id is null
 and page.siteid = pageSite.id
 and page.is_template=0
--Get the pages with a template (template_id not null)
union
select distinct
    'Page' as object_type,
    pageSite.name as page_group,
    page.name as name,
    portal.wwpob_page_util.get_page_url(page.id,page.siteid) as url,
    template.page_group || ' - ' || template.template_name as template_name,
    template.tab_name as template_tab,
    null as pageskin_name
from portal.wwpob_page$ page,
    portal.wwsbr_sites$ pageSite,
    ( select distinct
             template.id as id,
             templateSite.name as page_group,
             decode(parentTemplate.name, null, template.name, parentTemplate.name) as template_name,
             decode(parentTemplate.name, null, null, template.name) as tab_name
      from portal.wwpob_page$ template
           left outer join portal.wwpob_page$ parentTemplate
           on template.parentid = parentTemplate.id,
           portal.wwsbr_sites$ templateSite
      where template.is_template = 1
        and template.siteid = templateSite.id
    ) template
where page.is_template=0
 and page.template_id = template.id
 and page.siteid = pageSite.id
--Get the pages and templates with a skin (template == null skin != null)
union
select distinct
    decode(page.is_template,0,'Page',1,'Template') as object_type,
    pageSite.name as page_group,
    page.name as name,
    portal.wwpob_page_util.get_page_url(page.id,page.siteid) as url,
    null as template_name,
    null as template_tab,
    pageskinSite.name || ' - ' || pageskin.template_name as pageskin_name
from portal.wwpob_page$ page,
    portal.wwsbr_sites$ pageSite,
    portal.wwtmp_usr_template$ pageskin,
    portal.wwsbr_sites$ pageskinSite
where page.template_id is null
 and page.ui_template_id = pageskin.id
 and page.siteid = pageSite.id
 and pageskin.site_id = pageskinSite.id