This discussion is archived
2 Replies Latest reply: Sep 14, 2007 2:57 AM by 428239 RSS

Unable to use the thesaurus in a relaxation template

428239 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Barbara, that's fixed it.