MattsBits
MattsBits

MattsBits

Bulk Delete Empty Pages In Oracle Portal Using PLSQL  

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

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;

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