MattsBits
MattsBits

MattsBits

Find Pages With No Items In Oracle Portal  

by Matt Hawkins, 12/04/2011
Categories : Oracle

To track down pages in Oracle Portal that contain no content you can use the following query. It counts the items on a specified page and all its subpages. Those with a count of zero can then be identified.

First you need to define the following function :

create or replace
FUNCTION COUNT_PAGE_ITEMS(PPAGEID IN NUMBER) 
  RETURN NUMBER AS 

  -- This function counts the number of items
  -- on a specified page.
  -- It returns the count or zero.

  intCount NUMBER;

BEGIN

  SELECT COUNT(ID) INTO intCount 
    FROM WWV_THINGS
   WHERE CORNERID=PPAGEID;

  RETURN intCount;

EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END COUNT_PAGE_ITEMS;


This simply returns the item count for a specified page.

The query below can then be run to return the results :

SELECT
  id "ID",
  level "LEVEL",
  parentid "PARENTID",
  name "NAME",
  title "TITLE",
  COUNT_PAGE_ITEMS(id) "ITEM_COUNT",
  'http://www.example.com:7780/portal/page/mydb/' || guid "URL"

  FROM wwpob_page WHERE siteid=999 AND base_type=1
  
  CONNECT BY PRIOR id=parentid
  START WITH id=11111 AND siteid=999
  ORDER SIBLINGS BY TITLE;


where "11111" is the starting page id and "999" is the site id (page group id). Additional columns from the wwpob_page table can be added as required.

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017