When users have created hundreds of pages it is useful to be able to delete empty pages without have to search for them individually. This script can identify and delete empty pages using PLSQL. It just requires you to define the Site ID and the ID of the page to start from.

I have used this script successfully in SQL Developer.

A cursor is defined that identifies all the pages within a page tree, counts the items on those pages and returns those pages with a count of zero.

A for loop then uses the Portal API to delete those pages.

DECLARE



CURSOR c1(iSiteID NUMBER, iPageID NUMBER) IS 



SELECT * FROM

(SELECT level "LVL",

        id,

        parentid,

        title,

        COUNT_PAGE_ITEMS(id) "ITEM_COUNT",

        COUNT_PAGE_SUBPAGES(id) "SUBPAGE_COUNT"

 FROM wwpob_page$ WHERE siteid = iSiteID AND base_type = 1

 CONNECT BY PRIOR id = parentid

 START WITH id = iPageID AND siteid = iSiteID

 ORDER SIBLINGS BY title)

WHERE ITEM_COUNT=0 AND SUBPAGE_COUNT=0;



intSiteID NUMBER;

intPageID NUMBER;



BEGIN



-- EDIT THESE VALUES --



   intSiteID := 101;

   intPageID := 123456;



 -- EDIT THESE VALUES --



dbms_output.put_line('Looking for empty pages under page '

            || intPageID || ' in site ' || intSiteID);



FOR page IN c1(intSiteID,intPageID) LOOP



  -- Use API to delete empty page

  wwsbr_api.delete_folder(page.id,intSiteID);

  

  -- Output text to dbms output

  dbms_output.put_line('Delete : ' || page.ID || ' : ' || page.title);



END LOOP;



dbms_output.put_line('Completed');



EXCEPTION WHEN OTHERS THEN



  dbms_output.put_line('ERROR');



END;

When using this script for the first time it is best to comment out the wwsbr_api.delete_folder line, run the script and examine the dbms output. This will allow you to double check the script would have deleted the correct pages.

The script makes use of two custom functions COUNT_PAGE_ITEMS and COUNT_PAGE_SUBPAGES.

These are defined below :

COUNT_PAGE_ITEMS

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@PORTAL_SDE WHERE CORNERID=PPAGEID;

  RETURN intCount;



EXCEPTION WHEN OTHERS THEN

  RETURN 0;

END COUNT_PAGE_ITEMS;

COUNT_PAGE_SUBPAGES

create or replace

FUNCTION COUNT_PAGE_SUBPAGES(PPAGEID IN NUMBER) RETURN NUMBER AS 



  -- This function counts the number of subpages on a specified page.

  -- It returns the count or zero.



  intCount NUMBER;



BEGIN



  SELECT COUNT(ID) INTO intCount 

    FROM WWPOB_PAGE$ WHERE PARENTID=PPAGEID;

  RETURN intCount;



EXCEPTION WHEN OTHERS THEN

  RETURN 0;

END COUNT_PAGE_SUBPAGES;