2 Replies Latest reply: Sep 14, 2007 4:57 AM by 428239 RSS

    Unable to use the thesaurus in a relaxation template

    428239
      I am trying to get a query relaxation template to use the thesaurus but I can't get the syntax correct. Is it possible? If so, please can someone tell me where I'm going wrong?

      create table test_table(company_name varchar2(100));
      insert into test_table values ('Test Limited');
      insert into test_table values ('Test Ltd');

      create index idx_test on test_table(company_name) indextype is ctxsys.context;

      If my query looks like this:

      select company_name, score(1)
      from test_table
      where CONTAINS (company_NAME,
      '<query>
      <textquery lang="ENGLISH" grammar="CONTEXT">test ltd
      <progression>
      <seq><rewrite>transform((TOKENS, “{”, “}”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “!”, “%”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “${”, “}”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “SYN(”, “,legal_form)”, “ ”))</rewrite></seq>
      </progression>
      </textquery>
      <score datatype="INTEGER" algorithm="COUNT"/>
      </query>',1)>0;

      I get the matching record back

      COMPANY_NAME SCORE(1)
      Test Ltd 75

      But if I move the SYN line to the top like this:

      select company_name, score(1)
      from test_table
      where CONTAINS (company_NAME,
      '<query>
      <textquery lang="ENGLISH" grammar="CONTEXT">test ltd
      <progression>
      <seq><rewrite>transform((TOKENS, “SYN(”, “,legal_form)”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “{”, “}”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “!”, “%”, “ ”))</rewrite></seq>
      <seq><rewrite>transform((TOKENS, “${”, “}”, “ ”))</rewrite></seq>
      </progression>
      </textquery>
      <score datatype="INTEGER" algorithm="COUNT"/>
      </query>',1)>0;

      I get an error which I think means that the XML line is not valid:

      ORA-29902:error in executing ODCIIndexStart() routine
      ORA-20000: Oracle Text error:
      DRG-50901: text query parser syntax error on line 1, column 35

      What is the correct format for the line that will apply the thesaurus synonym between Limited to LTD?
        • 1. Re: Unable to use the thesaurus in a relaxation template
          Barbara Boehmer
          There are a lot of things that work well individually, but not in combination with one another. It looks like something goes wrong when you try to combine transform with syn. One possible workaround is to use replace to do your own transformation. Please see the reproduction and solution below.

          SCOTT@10gXE> -- test environment:
          SCOTT@10gXE> create table test_table(company_name varchar2(100));

          Table created.

          SCOTT@10gXE> insert into test_table values ('Test Limited');

          1 row created.

          SCOTT@10gXE> insert into test_table values ('Test Ltd');

          1 row created.

          SCOTT@10gXE> create index idx_test on test_table(company_name) indextype is ctxsys.context;

          Index created.

          SCOTT@10gXE> EXEC CTX_THES.CREATE_THESAURUS ('legal_form')

          PL/SQL procedure successfully completed.

          SCOTT@10gXE> EXEC CTX_THES.CREATE_RELATION ('legal_form', 'Limited', 'SYN', 'Ltd')

          PL/SQL procedure successfully completed.

          SCOTT@10gXE> COLUMN company_name FORMAT A30
          SCOTT@10gXE> -- reproduction of problem:
          SCOTT@10gXE> select company_name, score(1)
            2  from test_table
            3  where CONTAINS (company_NAME,
            4  '<query>
            5  <textquery lang="ENGLISH" grammar="CONTEXT">test ltd
            6  <progression>
            7  <seq><rewrite>transform((TOKENS, “SYN(”, “,legal_form)”, “ ”))</rewrite></seq>
            8  <seq><rewrite>transform((TOKENS, “{”, “}”, “ ”))</rewrite></seq>
            9  <seq><rewrite>transform((TOKENS, “!”, “%”, “ ”))</rewrite></seq>
          10  <seq><rewrite>transform((TOKENS, “${”, “}”, “ ”))</rewrite></seq>
          11  </progression>
          12  </textquery>
          13  <score datatype="INTEGER" algorithm="COUNT"/>
          14  </query>',1)>0
          15  /
          select company_name, score(1)
          *
          ERROR at line 1:
          ORA-29902: error in executing ODCIIndexStart() routine
          ORA-20000: Oracle Text error:
          DRG-50901: text query parser syntax error on line 1, column 7


          SCOTT@10gXE> -- possible workaround:
          SCOTT@10gXE> VARIABLE search_string VARCHAR2(30)
          SCOTT@10gXE> EXEC :search_string := 'test ltd'

          PL/SQL procedure successfully completed.

          SCOTT@10gXE> select company_name, score(1)
            2  from test_table
            3  where CONTAINS (company_NAME,
            4  '<query>
            5  <textquery lang="ENGLISH" grammar="CONTEXT">
            6  <progression>
            7  <seq>' || 'SYN(' || REPLACE(:search_string, ' ', ',legal_form) AND SYN(') || ',legal_form)' || '</seq>
            8  <seq>' || '{'    || REPLACE(:search_string, ' ', '} {')                 || '}'           || '</seq>
            9  <seq>' || '!'    || REPLACE(:search_string, ' ', '% !')                 || '%'           || '</seq>
          10  <seq>' || '${'   || REPLACE(:search_string, ' ', '} ${')                 || '}'           || '</seq>
          11  </progression>
          12  </textquery>
          13  <score datatype="INTEGER" algorithm="COUNT"/>
          14  </query>',1)>0
          15  /

          COMPANY_NAME                     SCORE(1)
          ------------------------------ ----------
          Test Limited                           75
          Test Ltd                               75

          SCOTT@10gXE>
          • 2. Re: Unable to use the thesaurus in a relaxation template
            428239
            Thanks Barbara, that's fixed it.