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,