Database Software

Hi Gurus,

Wild Card search is not working as intended, Could you please help me figure out where I am going wrong.

-- creating a table 
create table test_sh4 (text1 clob,text2 clob,text3 clob);

-- inserting some data into table
declare
	var1 clob := 'Garmin Approach G5 Golf GPS';
	var2 clob := 'Give your game a boost of confidence with Approach G5, a rugged, waterproof, touchscreen golf GPS packed with thousands of preloaded golf course maps. Approach uses a high-sensitivity GPS receiver to measure individual shot distances and show the exact yardage to fairways, hazards and greens. Approach displays and updates your exact position on stunningly detailed, preloaded course maps throughout the United States and Canada. Approach''s highly sensitive GPS receiver pinpoints your position and removes guesswork from your game. And as you move, Approach automatically updates your position, so you''ll always know your yardage. There are no subscriptions or setup fees, and Approach is compliant with USGA and R&A rules.';	
begin
	insert into test_sh4 values (var1,var2,'y');
	commit;
end;	

/

-- creating preferences 

begin
   ctx_ddl.create_preference ('test_mcd', 'multi_column_datastore'); -- utilizing the same index for multiple columns
    ctx_ddl.set_attribute
      ('test_mcd',
       'columns',
       'regexp_replace(text1,''[[:punct:]*|[[:space:]*]'') nd1, -- virtual column to eliminate white spaces and punctuation marks  in text1 column 
        text1 text1,  
        regexp_replace(text2,''[[:punct:]*|[[:space:]*]'') nd2,  -- virtual column to eliminate white spaces and punctuation marks   in text2 column 
        text2 text2');
    ctx_ddl.create_preference ('test_lex1', 'basic_lexer');
    ctx_ddl.set_attribute ('test_lex1', 'whitespace', '/\|-_+&'); --translating special characters as white space using lexer
    ctx_ddl.create_section_group ('test_sg', 'basic_section_group'); -- creating section group to search within sections.
    ctx_ddl.add_field_section ('test_sg', 'text1', 'text1', true);
    ctx_ddl.add_field_section ('test_sg', 'nd1', 'nd1', true);
    ctx_ddl.add_field_section ('test_sg', 'text2', 'text2', true);
    ctx_ddl.add_field_section ('test_sg', 'nd2', 'nd2', true);
	ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute('SUBSTRING_PREF','SUBSTRING_INDEX','TRUE');
	ctx_ddl.set_attribute('SUBSTRING_PREF','PREFIX_INDEX','TRUE');
	ctx_ddl.set_attribute('SUBSTRING_PREF','PREFIX_MIN_LENGTH', '3');
	ctx_ddl.set_attribute('SUBSTRING_PREF','PREFIX_MAX_LENGTH', '8');
	ctx_ddl.create_preference('textstore', 'BASIC_STORAGE');
    ctx_ddl.set_attribute('textstore', 'I_TABLE_CLAUSE','tablespace CAPT_DATA storage (initial 64K next 64K)');
    ctx_ddl.set_attribute('textstore', 'K_TABLE_CLAUSE','tablespace CAPT_DATA storage (initial 64K next 64K)');
	ctx_ddl.set_attribute('textstore', 'R_TABLE_CLAUSE','tablespace CAPT_DATA storage (initial 64K) lob (data) store as (cache)');
    ctx_ddl.set_attribute('textstore', 'N_TABLE_CLAUSE','tablespace CAPT_DATA storage (initial 64K)');
    ctx_ddl.set_attribute('textstore', 'I_INDEX_CLAUSE','tablespace CAPT_INDEX storage (initial 64K) compress 2');
    ctx_ddl.set_attribute('textstore', 'P_TABLE_CLAUSE','tablespace CAPT_DATA storage (initial 64K)');
end;
/


create index capone.IX_test_sh4  on test_sh4 (text3)
indextype is ctxsys.context
parameters
    ('datastore test_mcd
      lexer          test_lex1
      section group  test_sg  
	  wordlist SUBSTRING_PREF  
	  SYNC ( ON COMMIT)
	  storage textstore
	  MEMORY 50M '
	  );
	  
-- following query does not give any results 

SQL> SELECT SCORE(1) score,t.*
  2  FROM test_sh4 t WHERE CONTAINS (text3,' <query>
  3  <textquery>
  4  <progression>
  5  <seq>(%GOLFCOURSEMAP%) within nd1</seq>
  6  <seq>(%GOLFCOURSEMAP%) within nd2</seq>
  7  </progression>
  8  </textquery>
  9  <score datatype="FLOAT" algorithm="default"/>
 10  </query>',1) >1   ORDER BY score(1) DESC   ;

no rows selected

following query using regular expression function gives results.

SQL> select count(*) from  test_sh4 where upper( regexp_replace(text2,'[[:punct:]*|[[:space:]*)  like '%GOLFCOURSEMAP%';

  COUNT(*)
----------
         1

Please help me figure out why nd2 preference is not working as intended.

regexp_replace(text2,''[[:punct:]*|[[:space:]*]'') nd2, -- virtual column to eliminate white spaces in text2 column