Maclochlainn's Weblog

If you want to know what’s in the default or custom STOPLIST, you can use this set of tools to find, format, and output the words in a STOPLIST. This example requires you to unlock the CTXSYS account. Then, you must grant the SELECT privileges on the CTX_STOPWORDS table to your user.
 
You create a package that contains an associative array that is indexed by a single alphabetic character:

CREATE OR REPLACE PACKAGE tools IS
  TYPE list IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(1);
END tools;
/

You create a function to organize the list of excluded words alphabetically in an associative array:

CREATE OR REPLACE FUNCTION get_stoplist (stoplist VARCHAR2) RETURN TOOLS.LIST IS
  -- Define and declare collection.
  TYPE alpha_key IS TABLE OF CHARACTER;
  code ALPHA_KEY := alpha_key('A','B','C','D','E','F','G','H','I','J'
                             ,'K','L','M','N','O','P','Q','R','S','T'
                             ,'U','V','W','X','Y','Z');
  -- Define two collections.
  stopword TOOLS.LIST;
  -- Define cursor.
  CURSOR c (stoplist VARCHAR2) IS
    SELECT spw_word
    FROM ctxsys.ctx_stopwords
    WHERE spw_stoplist = UPPER(stoplist)
    ORDER BY spw_word;
BEGIN
  -- Initialize reserved and key word collections.
  FOR i IN 1..code.LAST LOOP
    FOR j IN c(stoplist) LOOP
      IF code(i) = UPPER(SUBSTR(j.spw_word,1,1)) THEN
        IF stopword.EXISTS(code(i)) THEN
          stopword(code(i)) := stopword(code(i)) || ', ' || j.spw_word;
        ELSE
          stopword(code(i)) := j.spw_word;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  RETURN stopword;
END get_stoplist;
/

You create a function to format the output at the SQL*Plus prompt:

CREATE OR REPLACE FUNCTION format_list (list_in TOOLS.LIST) RETURN BOOLEAN IS
  -- Declare control variables.
  current VARCHAR2(1);
  element VARCHAR2(2000);
  status BOOLEAN := TRUE;
BEGIN
  -- Read through an alphabetically indexed collection.
  IF list_in.COUNT > 0 THEN
    current := list_in.FIRST;
    element := list_in(current);
    WHILE current IS NOT NULL LOOP
      dbms_output.put_line(current||' '||element);
      current := list_in.NEXT(current);
      -- Prevent reading beyond the list with a NULL index value.
      IF current IS NOT NULL THEN
        element := list_in(current);
      END IF;
    END LOOP;
  END IF;
  RETURN status;
END format_list;
/

You call the formatted output using an anonymous block:

BEGIN
  IF format_list(get_stoplist('default_stoplist')) THEN
    NULL;
  END IF;
END;
/

You’ll see the following output:

There are permutations that would let you see XHTML formatted output. They’re left to your creativity.

The default STOPLIST is created by the $ORACLE_HOME/ctx/admin/defaults/drdeftr.sql script. You can add a word to the excluding STOPLIST by:

BEGIN
  CTX_DDL.ADD_STOPWORD('DEFAULT_STOPLIST','o');
END;
/