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

    Achieving functionality of many preferences using one context index

    876250
      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
          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-Oracle
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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