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.