Forum Stats

  • 3,815,709 Users
  • 2,259,070 Discussions
  • 7,893,213 Comments

Discussions

using SYN within NEAR (workaround 2)

Muzy
Muzy Member Posts: 2 Red Ribbon

Hi all,

there is a discussion about using SYN within NEAR years ago with a workaround, which is old and complex. I have a simple workaround for that issue.

Discussion :

Workaround :

Let's say we have the words A1,A2 and A3 which are synonyms and B1 and B2 which are also synonyms.

If you run the query like this :

SELECT .....

WHERE

CONTAINS(.....,  'NEAR( ( SYN(A1,THES_NAME) , SYN(A2,THES_NAME) ), 20 , TRUE )', .....

You get this error :

ORA-20000: Oracle Text error:

DRG-50901: text query parser syntax error on line ..., column ....

That means Oracle Text doesn't support SYN within NEAR.

Actually the old "Backward Compatibility NEAR Syntax" suppport it https://docs.oracle.com/cd/B28359_01/text.111/b28304/cqoper.htm#CCREF0313

Like this :

SYN(A1,THES_NAME) NEAR SYN(A2,THES_NAME)

But you can not change the default values of two other parameters. It will run with these deafult values max_span = 100 and order = FALSE.

Another workaround is to use EQUIValence (=) operator. Like this :

NEAR( ( A1=A2=A3 , B1=B2 ), 20 , TRUE )

That's the result we want to achieve. But how to get the synomyns of the terms A1 and B1 ?

We can use CTX_THES.SYN stored procedure.

select CTX_THES.SYN('A1',THES_NAME) from dual;

Output : {A1}|{A2}|{A3}

With this function we can have an output with EQUIValence (=) operator.

create or replace FUNCTION GET_SYNONYMS_AS_EQUIVALENCE

(

  TERM IN VARCHAR2

, THESAURUS_NAME IN VARCHAR2 DEFAULT 'DEFAULT'

)

RETURN VARCHAR2 AS

BEGIN

  declare

    EQUIVALENCE VARCHAR2(32767);

  begin

    select RTRIM(LTRIM(REPLACE(CTX_THES.SYN(TERM,THESAURUS_NAME),'}|{','='),'{'),'}')

    INTO EQUIVALENCE from dual;

    return EQUIVALENCE;

end;

END GET_SYNONYMS_AS_EQUIVALENCE;

Usage :

SELECT .....

WHERE

CONTAINS(.....,  'NEAR( '|| GET_SYNONYMS_AS_EQUIVALENCE('A1',THES_NAME)  || ' , '|| GET_SYNONYMS_AS_EQUIVALENCE('B1',THES_NAME)  || ' ), 20 , TRUE )', .....

dmak2709