SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (test_col VARCHAR2(60))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES ('word1 word2 word3')
  3  INTO test_tab VALUES ('word1 note™ word3')
  4  INTO test_tab VALUES ('word1 note™d word3')
  5  INTO test_tab VALUES ('word1 noted word3')
  6  INTO test_tab VALUES ('word1 notes word3')
  7  INTO test_tab VALUES ('word1 noteworthy word3')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_11g> SET DEFINE OFF
SCOTT@orcl_11g> DECLARE
  2    mainViewName VARCHAR2(4) := 'test';
  3    suffix	    VARCHAR2(3) := 'idx';
  4  BEGIN
  5    CTX_DDL.CREATE_PREFERENCE
  6  	 (mainViewName || '_' || suffix || '_SpecChLex',
  7  	  'BASIC_LEXER');
  8    ctx_ddl.set_attribute
  9  	 (mainViewName || '_' || suffix || '_SpecChLex',
 10  	  'skipjoins',
 11  	  '`-=[];''\,./~!@#$%^&*()_+{}:"|<>?§&acute;&uml;&frac12;&frac14;&frac34;&curren;£¤©™®');
 12    CTX_DDL.CREATE_PREFERENCE
 13  	 (mainViewName || '_' || suffix || '_wordlist',
 14  	  'BASIC_WORDLIST');
 15    CTX_DDL.SET_ATTRIBUTE
 16  	 (mainViewName || '_' || suffix || '_wordlist',
 17  	  'WILDCARD_MAXTERMS', 15000);
 18  END;
 19  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
  2  INDEXTYPE IS CTXSYS.CTXCAT
  3  PARAMETERS
  4    ('LEXER	  test_idx_SpecChLex
  5  	 WORDLIST test_idx_wordlist')
  6  /

Index created.

SCOTT@orcl_11g> COLUMN wmt HEADING 'WILDCARD_MAX_TERMS|MUST BE GREATER THAN THIS'
SCOTT@orcl_11g> SELECT COUNT (DISTINCT dr$token) AS wmt
  2  FROM  dr$test_idx$i WHERE dr$token LIKE 'NOTE%'
  3  /

       WILDCARD_MAX_TERMS
MUST BE GREATER THAN THIS
-------------------------
                        4

SCOTT@orcl_11g> SELECT DISTINCT dr$token FROM dr$test_idx$i
  2  /

DR$TOKEN
----------------------------------------------------------------
NOTE
NOTED
NOTES
NOTEWORTHY
WORD1
WORD2
WORD3

7 rows selected.

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CATSEARCH (test_col, 'note™*', NULL) > 0
  2  /

TEST_COL
------------------------------------------------------------
word1 note™ word3
word1 note™d word3
word1 noted word3
word1 notes word3
word1 noteworthy word3

SCOTT@orcl_11g> 

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT ('MV_SONG_SE_BATMAN_IND') FROM DUAL;

begin
  ctx_ddl.create_preference('"MV_SONG_SE_BATMAN_IND_LEX"','BASIC_LEXER');
  ctx_ddl.set_attribute('"MV_SONG_SE_BATMAN_IND_LEX"','SKIPJOINS','`-=[];'\,./~!@#$%^&*()_+{}:"|<>?§&acute;&uml;&frac12;&frac14;&frac34;&curren;£¿©¿®');
end;
/

begin
  ctx_ddl.create_preference('"MV_SONG_SE_BATMAN_IND_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"MV_SONG_SE_BATMAN_IND_WDL"','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('"MV_SONG_SE_BATMAN_IND_WDL"','FUZZY_MATCH','GENERIC');
end;
/

begin
  ctx_ddl.create_stoplist('"MV_SONG_SE_BATMAN_IND_SPL"','BASIC_STOPLIST');
end;
/

begin
  ctx_ddl.create_preference('"MV_SONG_SE_BATMAN_IND_STO"','BASIC_STORAGE');
  ctx_ddl.set_attribute('"MV_SONG_SE_BATMAN_IND_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)');
  ctx_ddl.set_attribute('"MV_SONG_SE_BATMAN_IND_STO"','I_INDEX_CLAUSE','compress 2');
end;
/

begin
  ctx_ddl.create_index_set('"MV_SONG_SE_BATMAN_IND_IXS"');
  ctx_ddl.add_index('"MV_SONG_SE_BATMAN_IND_IXS"','GENREID');
  ctx_ddl.add_index('"MV_SONG_SE_BATMAN_IND_IXS"','SORTORDER');
end;
/


begin
  ctx_output.start_log('MV_SONG_SE_BATMAN_IND_LOG');
end;
/

create index "MEDIA_CORE"."MV_SONG_SE_BATMAN_IND" 
  on "MEDIA_CORE"."MV_SONG_SE_BATMAN"
      ("SEARCHTEXT")
  indextype is ctxsys.ctxcat
  parameters('
    lexer           "MV_SONG_SE_BATMAN_IND_LEX"
    wordlist        "MV_SONG_SE_BATMAN_IND_WDL"
    stoplist        "MV_SONG_SE_BATMAN_IND_SPL"
    storage         "MV_SONG_SE_BATMAN_IND_STO"
    index set       "MV_SONG_SE_BATMAN_IND_IXS"
  ')
/

begin
  ctx_output.end_log;
end;
/

SELECT DISTINCT dr$token FROM dr$test_idx$i;
[COLOR=red]D[/COLOR]
NOTE
NOTED
NOTES
NOTEWORTHY
WORD1
WORD2
WORD3

SELECT * FROM test_tab WHERE CATSEARCH (test_col, 'note™*', NULL) > 0;
word1 note™ word3
word1 note™d word3

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT ('test_idx') FROM DUAL;

begin
  ctx_ddl.create_preference('"TEST_IDX_LEX"','BASIC_LEXER');
  ctx_ddl.set_attribute('"TEST_IDX_LEX"','SKIPJOINS','`-=[];'\,./~!@#$%^*()_+{}:"|<>?§£¤©™®');
end;
/

begin
  ctx_ddl.create_preference('"TEST_IDX_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"TEST_IDX_WDL"','WILDCARD_MAXTERMS','15000');
end;
/

begin
  ctx_ddl.create_stoplist('"TEST_IDX_SPL"','BASIC_STOPLIST');
  {removed the stopwords}
end;
/

begin
  ctx_ddl.create_preference('"TEST_IDX_STO"','BASIC_STORAGE');
  ctx_ddl.set_attribute('"TEST_IDX_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)');
  ctx_ddl.set_attribute('"TEST_IDX_STO"','I_INDEX_CLAUSE','compress 2');
end;
/

begin
  ctx_ddl.create_index_set('"TEST_IDX_IXS"');
end;
/


begin
  ctx_output.start_log('TEST_IDX_LOG');
end;
/

create index "MEDIA_CORE"."TEST_IDX" 
  on "MEDIA_CORE"."TEST_TAB"
      ("TEST_COL")
  indextype is ctxsys.ctxcat
  parameters('
    lexer           "TEST_IDX_LEX"
    wordlist        "TEST_IDX_WDL"
    stoplist        "TEST_IDX_SPL"
    storage         "TEST_IDX_STO"
    index set       "TEST_IDX_IXS"
  ')
/

begin
  ctx_output.end_log;
end;
/

SCOTT@orcl_11g> SELECT par_value
  2  FROM   ctx_parameters
  3  WHERE  par_name = 'DEFAULT_LEXER'
  4  /

PAR_VALUE
--------------------------------------------------------------------------------
CTXSYS.DEFAULT_LEXER

SCOTT@orcl_11g> SELECT prv_attribute, prv_value
  2  FROM   ctx_preference_values
  3  WHERE  prv_owner || '.' || prv_preference =
  4  	    (SELECT par_value
  5  	     FROM   ctx_parameters
  6  	     WHERE  par_name = 'DEFAULT_LEXER')
  7  /

PRV_ATTRIBUTE
------------------------------
PRV_VALUE
--------------------------------------------------------------------------------
WHITESPACE



SCOTT@orcl_11g>

SCOTT@orcl_11g> CREATE TABLE test_tab (test_col  VARCHAR2(1))
  2  /

Table created.

SCOTT@orcl_11g> INSERT	INTO test_tab
  2  SELECT  CHR (LEVEL)
  3  FROM    DUAL
  4  CONNECT BY LEVEL <= 256
  5  /

256 rows created.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
  3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'BASE_LETTER', 'YES');
  4    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'MIXED_CASE', 'YES');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('LEXER	   test_lex
  5  	 STOPLIST  CTXSYS.EMPTY_STOPLIST')
  6  /

Index created.

SCOTT@orcl_11g> COLUMN base_letter     FORMAT A11
SCOTT@orcl_11g> COLUMN original_values FORMAT A30
SCOTT@orcl_11g> SELECT i.token_text	      AS base_letter,
  2  	    WM_CONCAT (t.test_col) AS original_values
  3  FROM   test_tab t, dr$test_idx$i i
  4  WHERE  CONTAINS (t.test_col, i.token_text) > 0
  5  AND    t.test_col <> i.token_text
  6  GROUP  BY i.token_text
  7  ORDER  BY i.token_text
  8  /

BASE_LETTER ORIGINAL_VALUES
----------- ------------------------------
A           À,Á,Ã,Â,Ä,Å
C           Ç
E           È,Ê,É,Ë
I           Ì,Ï,Í,Î
N           Ñ
O           Ò,Õ,Ø,Ö,Ô,Ó
S           ¦
U           Ù,Ú,Û,Ü
Y           ¾,Ý
Z           ´
a           à,á,ã,å,ä,â
c           ç
e           è,ê,ë,é
i           ì,í,î,ï
n           ñ
o           ò,õ,ô,ó,ö,ø
s           ¨
u           ù,ú,ü,û
y           ý,ÿ
z           ¸

20 rows selected.

SCOTT@orcl_11g>

INSERT INTO t_tab
    SELECT CHR (LEVEL), level
      FROM DUAL
     WHERE length (CHR (LEVEL)) = 1
   CONNECT BY LEVEL <= 100000 /* lot of characters in the 50000 region */
;

SELECT i.token_text           AS base_letter,
       WM_CONCAT (t.test_col) AS original_values
  FROM t_tab t, dr$t_idx$i i
 WHERE CONTAINS (t.test_col, i.token_text) > 0
   AND ascii(t.test_col) <> ascii(i.token_text)
 GROUP BY i.token_text
 ORDER BY i.token_text;