select *
from my_table br
where br.name1 like '%12 34 567890%'
   or br.name2 like '%12 34 567890%'
   or br.name3 like '%12 34 567890%'

select *
from my_table br
where CONTAINS(br.name1, '%12 34 567890%') > 0
   or CONTAINS(br.name2, '%12 34 567890%') > 0
   or CONTAINS(br.name3, '%12 34 567890%') > 0

select *
from my_table br
where CONTAINS(br.name1, '%34 57 123890%') > 0

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

DECLARE
  PROCEDURE TryDropPreference(pPreference IN VARCHAR2)
  IS
  BEGIN
    FOR rec IN 
    (
      SELECT *
        FROM ctx_user_preferences p
       WHERE p.pre_name = UPPER(pPreference)
    )
    LOOP
      ctx_ddl.drop_preference(pPreference);
    END LOOP;
  END;
  
  PROCEDURE AddCtxIndex(pIndex IN varchar2, pTable IN VARCHAR2, pColumn IN VARCHAR2, pOnCommit IN VARCHAR2)
  IS
    lCnt NUMBER;
    lCommand VARCHAR2(4000 CHAR);
    lPref VARCHAR2(1000 CHAR);
    lLex VARCHAR2(1000 CHAR);
    lStore VARCHAR2(1000 CHAR);
  BEGIN
    SELECT COUNT(1)
      INTO lCnt
      FROM user_indexes i
     WHERE i.table_name = pTable
       AND i.index_name = pIndex
       AND i.index_type = 'DOMAIN';

    IF lCnt = 0 THEN
      lPref := 'PREF_'||pTable||'_'||pColumn;
      
      TryDropPreference(lPref);

      ctx_ddl.create_preference(lPref, 'BASIC_WORDLIST');
      ctx_ddl.set_attribute(lPref,'PREFIX_INDEX','TRUE');
      ctx_ddl.set_attribute(lPref,'SUBSTRING_INDEX', 'YES');
      ctx_ddl.set_attribute(lPref, 'WILDCARD_MAXTERMS', 50000);

      lLex := 'LEX_'||pTable||'_'||pColumn;
      
      TryDropPreference(lLex);

      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS','_-''"$');

      lStore := 'PREF_STORE_'||pTable||'_'||pColumn;
      
      TryDropPreference(lStore);

      ctx_ddl.create_preference(lStore,'BASIC_STORAGE');
      ctx_ddl.set_attribute(lStore,'I_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'K_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'R_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'N_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'I_INDEX_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');

      lCommand := '
      create index '||pIndex||' on '||pTable||' ('||pColumn||') indextype is ctxsys.context PARAMETERS (''lexer '||lLex
      ||' wordlist '||lPref||' storage '||lStore||' memory 200M '||pOnCommit||''')';

      EXECUTE IMMEDIATE lCommand;
      dbms_output.put_line(lCommand);
    END IF;
  END;
  
 
BEGIN
  AddCtxIndex('MY_TABLE_CTX','MY_TABLE','NAME1','');
END;
/

CONTAINS(br.name1, '%34 57 123890%') > 0

      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS',' _-''"$');

      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS','_-''"$');
      ctx_ddl.set_attribute(lLex,'SKIPJOINS',' ');
;

Posted via ActualForum NNTP Server 1.5

Posted via ActualForum NNTP Server 1.5

Posted via ActualForum NNTP Server 1.5

select *
from my_table br
where CONTAINS(br.name1, '%12 34 567890%') > 0
   or CONTAINS(br.name2, '%12 34 567890%') > 0
   or CONTAINS(br.name3, '%12 34 567890%') > 0

select *
from my_table br
where CONTAINS(br.name1, '12 AND 34 AND 567890') .... e.t.c.