OraFAQ Forum: Text & interMedia » Searching Synonyms

Home » Server Options » Text & interMedia » Searching Synonyms (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Searching Synonyms [message #640634]

Sun, 02 August 2015 00:27

orauser001
Messages: 13
Registered: April 2013
Location: us

Junior Member

Hi

We are creating a Oracle Text Search capability for a field containing company names. In this domain certain synonyms are very common, such as

Inc -> Incorporated
Ltd -> Limited
PLC -> Public Limited Company

So if one searches for
'Tata Steel Limited' - they should also find 'Tata Steel Ltd'
'Sigma Berl Public Limited Company' - they should also find 'Sigma Berl PLC'

What is the best way to achieve this?

I saw some simple examples of the oracle's Thesaurus feature - where i can set up the above as synonyms.

However how would i use that in my contains query. For example if user is entering 'Tata Steel Limited' - i want only the Limited word to be expanded to its equivalent words (not the words Tata and Steel)

Thanks in advance for any pointers

Report message to a moderator

Re: Searching Synonyms [message #640635 is a reply to message #640634]

Sun, 02 August 2015 01:41

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks to feedback and thanks people who help you something you NEVER done in your previous topics.

Report message to a moderator

Re: Searching Synonyms [message #640637 is a reply to message #640634]

Sun, 02 August 2015 05:08

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

You can separate the user input of the name and suffix as demonstrated below.

SCOTT@orcl> CREATE TABLE test_tab
  2    (company_name  VARCHAR2(60))
  3  /

Table created.

SCOTT@orcl> INSERT ALL
  2  INTO test_tab VALUES ('Tata Steel Ltd')
  3  INTO test_tab VALUES ('Sigma Berl PLC')
  4  INTO test_tab VALUES ('Oracle')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl> CREATE INDEX test_idx ON test_tab (company_name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl> BEGIN
  2    CTX_THES.CREATE_THESAURUS ('TEST_THES');
  3    CTX_THES.CREATE_RELATION ('TEST_THES', 'Limited', 'SYN', 'Ltd');
  4    CTX_THES.CREATE_RELATION ('TEST_THES', 'Public Limited Company', 'SYN', 'PLC');
  5    CTX_THES.CREATE_RELATION ('TEST_THES', 'Incorporated', 'SYN', 'Inc');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl> VARIABLE name_input VARCHAR2(60)
SCOTT@orcl> VARIABLE suffix_input VARCHAR2(30)
SCOTT@orcl> EXEC :name_input := 'Tata Steel'

PL/SQL procedure successfully completed.

SCOTT@orcl> EXEC :suffiX_input := 'Limited'

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT * FROM test_tab
  2  WHERE  CONTAINS
  3  	      (company_name,
  4  	       REPLACE (:name_input, ' ' , ',') ||
  5  	       ',SYN(' || :suffix_input || ',TEST_THES)') > 0
  6  /

COMPANY_NAME
------------------------------------------------------------
Tata Steel Ltd

1 row selected.

SCOTT@orcl> EXEC :name_input := 'Sigma Berl'

PL/SQL procedure successfully completed.

SCOTT@orcl> EXEC :suffiX_input := 'Public Limited Company'

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT * FROM test_tab
  2  WHERE  CONTAINS
  3  	      (company_name,
  4  	       REPLACE (:name_input, ' ' , ',') ||
  5  	       ',SYN(' || :suffix_input || ',TEST_THES)') > 0
  6  /

COMPANY_NAME
------------------------------------------------------------
Sigma Berl PLC

1 row selected.

Report message to a moderator

Re: Searching Synonyms [message #640644 is a reply to message #640637]

Sun, 02 August 2015 11:12

orauser001
Messages: 13
Registered: April 2013
Location: us

Junior Member

Thanks very much Barbara!

I had one follow-up question, your solution would work great if i want the search to look for individual words in the search query entered by the user (along with synonyms of the Ltd). However is there a way to make this query still act like a phrase query.

So logically speaking, i am looking for ability to do a search with a phrase like 'Tata Steel SYN(Limited)' - where i want oracle text to interpret this as the phrase 'Tata Steel' immediately followed by all synonyms of 'Limited'. I tried a couple of options which i am giving below - None of them work

BEGIN
 CTX_THES.CREATE_THESAURUS ('TEST_THES');
 CTX_THES.CREATE_RELATION ('TEST_THES', 'Limited', 'SYN', 'Ltd');
 CTX_THES.CREATE_RELATION ('TEST_THES', 'Public Limited Company', 'SYN', 'PLC');
 CTX_THES.CREATE_RELATION ('TEST_THES', 'Incorporated', 'SYN', 'Inc');
END;

drop table test_tab;
CREATE TABLE test_tab
 (company_name  VARCHAR2(60))
;
INSERT ALL
INTO test_tab VALUES ('Tata Steel Ltd')
INTO test_tab VALUES ('Tata Steel Limited')
INTO test_tab VALUES ('Tata Sponge Steel Jamshedpur Ltd')
INTO test_tab VALUES ('Sigma Berl PLC')
INTO test_tab VALUES ('Oracle')
SELECT * FROM DUAL
;
commit;
CREATE INDEX test_idx ON test_tab (company_name)
INDEXTYPE IS CTXSYS.CONTEXT
;

Your Query is in effect doing this

SELECT * FROM test_tab
WHERE  CONTAINS (company_name,'tata, steel, syn(Limited,TEST_THES)',1) > 0;

As expected it returns
COMPANY_NAME
Tata Steel Ltd
Tata Steel Limited
Tata Sponge Steel Jamshedpur Ltd

as you can see it returns 'Tata Sponge Steel Jamshedpur Ltd' also.
To make it work as a phrase query as i explained above I tried some options given below (but none of them work!)

Option-1

SELECT * FROM test_tab
WHERE  CONTAINS (company_name,'tata steel syn(Limited,TEST_THES)',1) > 0;

This gives:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50920: part of phrase not itself a phrase or equivalence

Option-2

SELECT * FROM test_tab
WHERE contains (company_name, 'near((tata steel, syn(Limited,TEST_THES)), 0)',1) > 0

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

Would appreciate if you can share any insights on possible ways to meet our requirement ...

Report message to a moderator

Re: Searching Synonyms [message #640685 is a reply to message #640644]

Mon, 03 August 2015 12:35

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl> CREATE TABLE test_tab
  2    (company_name  VARCHAR2(60))
  3  /

Table created.

SCOTT@orcl> INSERT ALL
  2  INTO test_tab VALUES ('Tata Steel Ltd')
  3  INTO test_tab VALUES ('Tata Steel Limited')
  4  INTO test_tab VALUES ('Tata Sponge Steel Jamshedpur Ltd')
  5  INTO test_tab VALUES ('Sigma Berl PLC')
  6  INTO test_tab VALUES ('Oracle')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl> CREATE INDEX test_idx ON test_tab (company_name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl> BEGIN
  2    CTX_THES.CREATE_THESAURUS ('TEST_THES');
  3    CTX_THES.CREATE_RELATION ('TEST_THES', 'Limited', 'SYN', 'Ltd');
  4    CTX_THES.CREATE_RELATION ('TEST_THES', 'Public Limited Company', 'SYN', 'PLC');
  5    CTX_THES.CREATE_RELATION ('TEST_THES', 'Incorporated', 'SYN', 'Inc');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT * FROM test_tab
  2  WHERE  CONTAINS (company_name, '(Tata Steel) AND SYN(limited,TEST_THES)') > 0
  3  /

COMPANY_NAME
------------------------------------------------------------
Tata Steel Ltd
Tata Steel Limited

2 rows selected.

SCOTT@orcl> VARIABLE name_input VARCHAR2(60)
SCOTT@orcl> VARIABLE suffix_input VARCHAR2(30)
SCOTT@orcl> EXEC :name_input := 'Tata Steel'

PL/SQL procedure successfully completed.

SCOTT@orcl> EXEC :suffiX_input := 'Limited'

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT * FROM test_tab
  2  WHERE  CONTAINS
  3  	      (company_name,
  4  	       '(' || :name_input || ') AND ' ||
  5  	       'SYN(' || :suffix_input || ',TEST_THES)') > 0
  6  /

COMPANY_NAME
------------------------------------------------------------
Tata Steel Ltd
Tata Steel Limited

2 rows selected.

Report message to a moderator

Re: Searching Synonyms [message #640689 is a reply to message #640685]

Mon, 03 August 2015 13:46

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Actually, the above would still return

Tata Steel something Limited

The following would be a more accurate method. I have provided some intermediary results for better understanding.

SCOTT@orcl> CREATE TABLE test_tab
  2    (company_name  VARCHAR2(60))
  3  /

Table created.

SCOTT@orcl> INSERT ALL
  2  INTO test_tab VALUES ('Tata Steel Ltd')
  3  INTO test_tab VALUES ('Tata Steel Limited')
  4  INTO test_tab VALUES ('Tata Sponge Steel Jamshedpur Ltd')
  5  INTO test_tab VALUES ('Sigma Berl PLC')
  6  INTO test_tab VALUES ('Oracle')
  7  INTO test_tab VALUES ('Tata Steel something Limited')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl> CREATE INDEX test_idx ON test_tab (company_name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl> BEGIN
  2    CTX_THES.CREATE_THESAURUS ('TEST_THES');
  3    CTX_THES.CREATE_RELATION ('TEST_THES', 'Limited', 'SYN', 'Ltd');
  4    CTX_THES.CREATE_RELATION ('TEST_THES', 'Public Limited Company', 'SYN', 'PLC');
  5    CTX_THES.CREATE_RELATION ('TEST_THES', 'Incorporated', 'SYN', 'Inc');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl> VARIABLE name_input   VARCHAR2(100)
SCOTT@orcl> VARIABLE suffix_input VARCHAR2(100)
SCOTT@orcl> EXEC :name_input   := 'Tata Steel'

PL/SQL procedure successfully completed.

SCOTT@orcl> EXEC :suffiX_input := 'Limited'

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT CTX_THES.SYN (:suffix_input, 'TEST_THES') FROM DUAL
  2  /

CTX_THES.SYN(:SUFFIX_INPUT,'TEST_THES')
--------------------------------------------------------------------------------
{LIMITED}|{LTD}

1 row selected.

SCOTT@orcl> CREATE OR REPLACE FUNCTION format_input
  2    (p_name_input   IN VARCHAR2,
  3  	p_suffix_input IN VARCHAR2)
  4    RETURN		  VARCHAR2
  5  AS
  6    v_suffixes	  VARCHAR2(4000);
  7    v_search 	  VARCHAR2(4000);
  8  BEGIN
  9    v_suffixes := CTX_THES.SYN (p_suffix_input, 'TEST_THES') || '|';
 10    WHILE LENGTH (v_suffixes) > 0 LOOP
 11  	 v_search := v_search || p_name_input || ' ' || SUBSTR (v_suffixes, 1, INSTR (v_suffixes, '|'));
 12  	 v_suffixes := SUBSTR (v_suffixes, INSTR (v_suffixes, '|') + 1);
 13    END LOOP;
 14    RETURN RTRIM (v_search, '|');
 15  END format_input;
 16  /

Function created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> SELECT format_input (:name_input, :suffix_input) FROM DUAL
  2  /

FORMAT_INPUT(:NAME_INPUT,:SUFFIX_INPUT)
--------------------------------------------------------------------------------
Tata Steel {LIMITED}|Tata Steel {LTD}

1 row selected.

SCOTT@orcl> SELECT * FROM test_tab
  2  WHERE  CONTAINS (company_name, format_input (:name_input, :suffix_input)) > 0
  3  /

COMPANY_NAME
------------------------------------------------------------
Tata Steel Ltd
Tata Steel Limited

2 rows selected.

[Updated on: Mon, 03 August 2015 13:48]

Report message to a moderator

Re: Searching Synonyms [message #640971 is a reply to message #640689]

Fri, 07 August 2015 08:24

orauser001
Messages: 13
Registered: April 2013
Location: us

Junior Member

Thanks Barbabra for your advise. Very useful for me.

Do want to comment though, based on the above solution, it seems that if one wants to do phrase searches - the oracle's SYNONYM feature is pretty useless, as in the solution above, the best we can do use the SYNONYM feature as a place to store the synonyms, everything else is custom ...

and storing SYNONYM we can do in any other table also ...

do you agree with this assessment ?

Report message to a moderator

Re: Searching Synonyms [message #640973 is a reply to message #640971]

Fri, 07 August 2015 13:12

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The Oracle Text SYNONYM query operator works in conjunction with some things like AND and OR. I can't figure out why it doesn't seem to work as part of a phrase. I tried adding parentheses around the preceding phrase and some other experiments, but couldn't get it to work. It could be that I am missing something or this is a bug or expected behavior. You might try posing the question on the OTN Text forum. I have provided a link below.

https://community.oracle.com/community/database/text/content

Report message to a moderator

Previous Topic:	which index is good ctxsys.context/ctxsys.CTXCAT
Next Topic:	ORA-29849 exception

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 06:57:22 CST 2024