Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Text CONTAINS in user_datastore returns wrong results for mutliple AND tokens

User_KDHMWFeb 10 2021

I have a User_datastore defined based on a procedure that collects data from various tables and indexes them. The column that contains the oracle text is pub_notification_mv.index_search_flag_publication.
The query:

select pub_notification_mv.NOTIFICATION_ID from pub_notification_mv  where contains(pub_notification_mv.index_search_flag_publication,'%COMME% AND %LANGUAGEISEN%') > 0 ` returns 1 row => 10000005 instead of 0 
select pub_notification_mv.NOTIFICATION_ID from pub_notification_mv  where contains(pub_notification_mv.index_search_flag_publication,'%COMME% AND %LANGUAGEISFR%') > 0 ` returns 1 which is valid => 10000005

The select inside the procedure returns
NOTIFICATION_ID LANGUAGE DESCRIPTION
10000005 LANGUAGEISFR comme tu habbitue
10000005LANGUAGEISEN olympiacos pireaus product small chain

SELECT * FROM DR$FTS_INDEX_PUBLICATION$I WHERE dr$fts_index_publication$i.token_text LIKE '%10000005%' returns:

TOKEN_TEXT
BRAND10000004A12/00001/2110000005TESTTESTLANGUAGEISFRCOMME
BRAND10000004A12/00001/2110000005TESTTESTLANGUAGEISENOLYMPIACOS
I have tried different combinations with CONTAINS and AND. I also tried the Structured Query with CONTAINS but still does not work.
Thanks,

Comments

Post Details

Added on Feb 10 2021
3 comments
224 views