CONTAINS doesn't work with Oracle Text

Question

I am having an issue executing this query.

SELECT * FROM gob_attachment 
WHERE CONTAINS (gob_a_document, 'java') > 0

It's giving me

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error: 
ORA-00942: table or view does not exist

29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
       take appropriate action.

After some googling I've disovered that problem could be in index, but when I looked at the table and index they seemed ok to me.

Create script for index is looking like this

CREATE INDEX FTSI_GOB_A_DOCUMENT 
 ON GOB_ATTACHMENT (GOB_A_DOCUMENT) 
 INDEXTYPE IS CTXSYS.CONTEXT;

Only thing which is strange to me is that when I look to Edit Table in Table properties in SQL Developer I can see that Operational Status of index is FAILED. Does anyone know what does it mean?Maybe DB rights?

Also it's working when I use

dbms_lob.instr(gob_a_document, utl_raw.cast_to_raw('java')) > 0

instead of contains

Thanks for any advice

P.S. It's related to my previous question Oracle DBMS_LOB.INSTR and CONTAINS performance

UPDATE

After recreating the index and some playing around, I've disovered that I can execute the query above, but it won't return me anything.

If I try it with CONTAINS(gob_a_document, '%'), the result is 26 rows and don't know by which key the query selected them(at least I didn't find anything obvious, I'll investigate it more). Problem could be in that we are using Oracle 10g and storing DOCX files which are supported from version 11.

Answer 1

"Operational Status of index is FAILED"

Okay, this means your CREATE INDEX statement failed. Did you get an error message? I guess the answer is yes but you missed it. So what you need to do is:

• drop the index
• re-run the CREATE INDEX statement
• if it throws an error, make a note of the reason and fix it

In case it's not obvious, the other statement runs because it's not attempting to use your CONTEXT index. It's doing the search the hard way.

---

"The thing was that the index was already created in DB,"

No the real thing was the index failed to create properly, hence its status. You could just have rebuilt the index, but it is usually better to fix the underlying cause of failure first. That's why I advised you to drop and recreate. Obviously the original failure was due to some ambient condition which no longer applies.

---

"Now the query is executed, but it's not giving me any results(0 rows returned). (and I am sure that it should return like 100 rows) "

So that sounds like you are storing documents in a binary format. What sort of documents? Are they in a supported format? That will depend upon which version of Oracle you're using. For instance, Oracle Text 10g supports up t Word 2003 (i.e. DOC only) whereas Oracle Text 11g supports Word 2007 (i.e. DOCX as well).