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.