We help IT Professionals succeed at work.

Oracle Text Contains Problem

leclaude
leclaude asked
on
Medium Priority
630 Views
Last Modified: 2013-12-19
Hi Everyone,
I'm using the Oracle Text contains function, but it sometimes doesn't work properly.  It returns 0 (text not found) when the text is actually there.

For example:

select e.* , contains(e.emp_name, '%Jones%') as cont_val
from emp_table e
where e.emp_name = 'Jones'

Returns a row with cont_val = 0 (which means text not found).  Results for %Jones% in the contains function in the where clause return no rows.  This is a sporadic problem that only occurs for some rows in our table.  We're using Oracle 10.2.0.1.0.
Any ideas what's going on?
Thanks

Comment
Watch Question

Top Expert 2009

Commented:
How are you updating your text indexes? They are not immediately synched and must be maintained, unlike regular indexes.

http://download.oracle.com/docs/cd/B15595_01/content.101/b14493/text.htm#sthref776

See the docs on ctx_ddl.sync_index() for manually synching, try a manual sync and see if it shows.

Author

Commented:
Why would an index need to be synched to return a row? At worst the query should be slow, but not miss results.
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.