This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Jan 9, 2012 1:10 PM by 876250 RSS

Achieving functionality of many preferences using one context index

876250 Newbie
Currently Being Moderated
Hi Gurus,

We have a table of following format
SCOTT@orcl_11gR2> insert all
2 into test_sh values (1, 'Mark Antony')
3 into test_sh values (2, 'Mark-Antony')
4 into test_sh values (3, 'markantony' )
5 into test_sh values (4, 'markantony' )
6 into test_sh values (5, 'Cleopatra' )
7 select * from dual
8 /
We need to achieve following functionality in this table

1) fuzzy
2) stem
3) synonym
4) near
5) soundex
6) As mentioned in thread Text Search help using ndata section
7) As mentioned in thread Re: Phrase Serach with special charaters in between using a lexer preference

Please guide me as to how to achieve everything in one shot.

I was thinking to write individual select statement and then do a distinct of id as given below but if we need to achieve #6 and #7 we need to add
a preference and other queries for sundex,stem etc will not work properly. Please guide me how I can achieve all functionality using one index.

> select * from test_sh where id in
select distinct id from (
select id from test_sh where contains (text_nm ,'markantony') > 0
union
select id from test_sh where contains (text_nm ,fuzzy('markantony')) > 0
union
select id from test_sh where contains (text_nm ,fuzzy('!markantony')) > 0
select id from test_sh where contains (text_nm ,fuzzy('$markantony')) > 0
)
Thanks & Regards,
Vikas Krishna
  • 1. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    SCOTT@orcl_11gR2> create table test_sh
      2    (text_id  number,
      3       text      clob)
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> insert all
      2  into test_sh values (1, 'Mark Antony')
      3  into test_sh values (2, 'Mark-Antony')
      4  into test_sh values (3, 'markantony' )
      5  into test_sh values (4, 'Cleopatra' )
      6  select * from dual
      7  /
    
    4 rows created.
    
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
      3    ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd, text text');
      4    ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
      5    ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
      6    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
      7    ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> create index ix_test_sh
      2  on test_sh (text)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore  nd_mcds
      6        section    group nd_sg
      7        lexer         test_lex')
      8  /
    
    Index created.
    
    SCOTT@orcl_11gR2> variable search_string varchar2(100)
    SCOTT@orcl_11gR2> exec :search_string := 'markantony'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> column text format a30
    SCOTT@orcl_11gR2> select * from test_sh
      2  where  contains
      3             (text,
      4              'ndata (nd,' || :search_string || ') or
      5            ctx_thes.syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0
      6  /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 Mark-Antony
             3 markantony
    
    3 rows selected.
  • 2. Re: Achieving functionality of many preferences using one context index
    user520754 Newbie
    Currently Being Moderated
    Did you really mean to use ctx_thes.syn in the contains clause or just the syn operator. Would this also require a (default) thesaurus to be loaded
  • 3. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    I should have used SYN. In either case, it would use the default thesaurus, unless you specifiy a user thesaurus as a parameter. I was just trying to provide a simple example.
  • 4. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    Thanks a lot Barbara for helping me out here.

    Except two requirements all my requirements were achieved.

    1) If I insert the following data
    Insert into test_sh values (14,'MarkAntony');
    Insert into test_sh values (15,'MA RK ANTONY');
    Insert into test_sh values (16,'m a r k antony');
    And try to query

    >select * from test_sh
    where contains
         (text,
         'ndata (nd,' || 'MA RK ANTONY' || ') or
              ctx_thes.syn (?!$' || replace (     'MA RK ANTONY', ' ', ';?!$') || ')') > 0
    I get following error
         ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: Oracle Text error: DRG-50900: text query parser error on line 2, column 39 DRG-50920: >part of phrase not itself a phrase or equivalence
    What I am expecting here is if I query for any of Mark Antony, MARK ANTONY,mark antony,MarkAntony,MA RK ANTONY,m a r k antony
    all the above results Mark Antony, MARK ANTONY,mark antony,MarkAntony,MA RK ANTONY,m a r k antony should be returned


    2) Another major problem is my database version is 11.1.0.7 so this query is not working there, I tried the same query in 11.2.0.2 and it works like a charm. Is there any alternative to achieve the same in 11.1.0.7.


    Thanks & Regards,
    Vikas Krishna
  • 5. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    I do not get the errror that you get and I get the correct results, as shown below. Please post a copy and paste of a complete test case, run from SQL*Plus, as I have done below.
    SCOTT@orcl_11gR2> create table test_sh
      2    (text_id  number,
      3       text      clob)
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> insert all
      2  into test_sh values (1, 'Mark Antony')
      3  into test_sh values (2, 'MARK ANTONY')
      4  into test_sh values (3, 'mark antony')
      5  into test_sh values (4, 'MarkAntony')
      6  into test_sh values (5, 'MA RK ANTONY')
      7  into test_sh values (6, 'm a r k antony')
      8  into test_sh values (7, 'Cleopatra' )
      9  select * from dual
     10  /
    
    7 rows created.
    
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
      3    ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd, text text');
      4    ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
      5    ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
      6    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
      7    ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> create index ix_test_sh
      2  on test_sh (text)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore  nd_mcds
      6        section    group nd_sg
      7        lexer         test_lex')
      8  /
    
    Index created.
    
    SCOTT@orcl_11gR2> variable search_string varchar2(100)
    SCOTT@orcl_11gR2> column text format a30
    SCOTT@orcl_11gR2> exec :search_string := 'Mark Antony'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> select * from test_sh
      2  where  contains
      3             (text,
      4              'ndata (nd,' || :search_string || ') or
      5            syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0
      6  /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
    
    SCOTT@orcl_11gR2> exec :search_string := 'MARK ANTONY'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
    
    SCOTT@orcl_11gR2> exec :search_string := 'mark antony'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
    
    SCOTT@orcl_11gR2> exec :search_string := 'MarkAntony'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
    
    SCOTT@orcl_11gR2> exec :search_string := 'MA RK ANTONY'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
    
    SCOTT@orcl_11gR2> exec :search_string := 'm a r k antony'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
       TEXT_ID TEXT
    ---------- ------------------------------
             1 Mark Antony
             2 MARK ANTONY
             3 mark antony
             4 MarkAntony
             5 MA RK ANTONY
             6 m a r k antony
    
    6 rows selected.
  • 6. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Here is my test script that I used for the post above. If you copy and paste it, then you can be sure that you are running exactly the same thing and any differences are due to versions and not typing errors or missing something.
    create table test_sh 
      (text_id  number,
       text     clob) 
    /
    insert all
    into test_sh values (1, 'Mark Antony')
    into test_sh values (2, 'MARK ANTONY')
    into test_sh values (3, 'mark antony')
    into test_sh values (4, 'MarkAntony')
    into test_sh values (5, 'MA RK ANTONY')
    into test_sh values (6, 'm a r k antony')
    into test_sh values (7, 'Cleopatra' )
    select * from dual
    /
    begin
      ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
      ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd, text text');
      ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
      ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
      ctx_ddl.create_preference ('test_lex', 'basic_lexer');
      ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
    end;
    / 
    create index ix_test_sh
    on test_sh (text)
    indextype is ctxsys.context
    parameters
      ('datastore  nd_mcds
        section    group nd_sg
        lexer      test_lex')
    /
    variable search_string varchar2(100)
    column text format a30
    exec :search_string := 'Mark Antony'
    select * from test_sh
    where  contains 
             (text, 
              'ndata (nd,' || :search_string || ') or 
               syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0
    / 
    exec :search_string := 'MARK ANTONY'
    /
    exec :search_string := 'mark antony'
    /
    exec :search_string := 'MarkAntony'
    /
    exec :search_string := 'MA RK ANTONY'
    /
    exec :search_string := 'm a r k antony'
    /
  • 7. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    Current database which I am trying these examples is of version 11.2.0.2


    SQL> select * from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production



    create table test_sh
    (text_id number,
    text clob)
    /
    insert all
    into test_sh values (1, 'Mark Antony')
    into test_sh values (2, 'MARK ANTONY')
    into test_sh values (3, 'mark antony')
    into test_sh values (4, 'MarkAntony')
    into test_sh values (5, 'MA RK ANTONY')
    into test_sh values (6, 'm a r k antony')
    into test_sh values (7, 'Cleopatra' )
    select * from dual
    /
    begin
    ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
    ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd, text text');
    ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
    ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
    ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
    end;
    /
    create index ix_test_sh
    on test_sh (text)
    indextype is ctxsys.context
    parameters
    ('datastore nd_mcds
    section group nd_sg
    lexer test_lex')
    /

    SQL> variable search_string varchar2(100)
    column text format a30
    exec :search_string := 'Mark Antony'SQL> SQL>

    PL/SQL procedure successfully completed.


    SQL> select * from test_sh
    where contains
    (text,
    'ndata (nd,' || :search_string || ') or
    syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0 2 3 4 5
    6 /
    select * from test_sh
    *
    ERROR at line 1:
    ORA-29902: error in executing ODCIIndexStart() routine
    ORA-20000: Oracle Text error:
    DRG-11702: thesaurus DEFAULT does not exist


    SQL> select * from test_sh
    where contains
    (text,
    'ndata (nd,' || :search_string || ') or
    ctx_thes.syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0 2 3 4 5
    6 /
    select * from test_sh
    *
    ERROR at line 1:
    ORA-29902: error in executing ODCIIndexStart() routine
    ORA-20000: Oracle Text error:
    DRG-50900: text query parser error on line 2, column 44
    DRG-50920: part of phrase not itself a phrase or equivalence
    Kindly let me know were I am going wrong.

    Thanks & Regards,
    Vikas Krishna
  • 8. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    Also Oracle Text version is also 11.2.0.2


    SQL> select comp_id,COMP_NAME,version,status from dba_registry where comp_name='Oracle Text';


    COMP_ID COMP_NAME VERSION STATUS
    ----------------------------------------------------------------------------
    CONTEXT Oracle Text 11.2.0.2.0 VALID
    Edited by: 873247 on Dec 9, 2011 12:13 PM
  • 9. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    Also the database we are going to implement this search is a 11.1.0.7 database, So is there any way to achieve these functionality there.


    Thanks & Regards,
    Vikas Krishna
  • 10. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    What tool are you using to run the script? It obviously isn't SQL*Plus and that is probably the problem. Please test it from SQL*Plus, as previously requested.
  • 11. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Also, why do you have:
    0 2 3 4 5
    6 /

    instead of:
    0
    /
  • 12. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    Creation of preferences, table and index were done in SQL WORKSHOP( ORACLE APEX)
    and the query was executed in SQLPLUS itself. I will try doing everything in SQLPLUS.
  • 13. Re: Achieving functionality of many preferences using one context index
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    The query does not appear to have been executed in SQL*Plus or it would have line numbers, like the query execution that I posted. Please make sure that you are using SQL*Plus and not something similar from APEX. You should also check your SQL*Plus version.
  • 14. Re: Achieving functionality of many preferences using one context index
    876250 Newbie
    Currently Being Moderated
    SQL> create table test_sh
    (text_id number,
    text clob)
    / 2 3 4

    Table created.

    SQL> insert all
    2 into test_sh values (1, 'Mark Antony')
    3 into test_sh values (2, 'MARK ANTONY')
    4 into test_sh values (3, 'mark antony')
    5 into test_sh values (4, 'MarkAntony')
    6 into test_sh values (5, 'MA RK ANTONY')
    7 into test_sh values (6, 'm a r k antony')
    8 into test_sh values (7, 'Cleopatra' )
    9 select * from dual
    10 /

    7 rows created.

    SQL> commit;

    Commit complete.

    SQL> begin
    2 ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
    3 ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd, text text');
    4 ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
    5 ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
    6 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
    7 ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
    8 end;
    9 /

    PL/SQL procedure successfully completed.


    SQL> create index ix_test_sh on test_sh (text) indextype is ctxsys.context parameters ('datastore nd_mcds section group nd_sg lexer test_lex');

    Index created.

    SQL> variable search_string varchar2(100)
    column text format a30
    exec :search_string := 'Mark Antony'SQL> SQL>

    PL/SQL procedure successfully completed.


    SQL> select * from test_sh
    where contains
    (text,
    'ndata (nd,' || :search_string || ') or
    syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0 2 3 4 5
    6 /
    select * from test_sh
    *
    ERROR at line 1:
    ORA-29902: error in executing ODCIIndexStart() routine
    ORA-20000: Oracle Text error:
    DRG-11702: thesaurus DEFAULT does not exist



    SQL> select * from test_sh
    where contains
    (text,
    'ndata (nd,' || :search_string || ') or
    ctx_thes.syn (?!$' || replace (:search_string, ' ', ';?!$') || ')') > 0 2 3 4 5
    6 /
    select * from test_sh
    *
    ERROR at line 1:
    ORA-29902: error in executing ODCIIndexStart() routine
    ORA-20000: Oracle Text error:
    DRG-50900: text query parser error on line 2, column 33
    DRG-50920: part of phrase not itself a phrase or equivalence
    I am not sure as to why 0 2 3 4 5 is coming up.
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points