2 Replies Latest reply on May 12, 2014 5:12 AM by Jta

    Oracle Text search returning no results

    Jta

      Hi,

       

      I have created an oracle text (ctxsys.context) on a BLOB column which has various documents of .doc,.docx,.pdf, types.

       

      CREATE INDEX IND_RESUME_DETAILS ON RESUME_DETAILS (RESUME)

         INDEXTYPE IS CTXSYS.CONTEXT

      PARAMETERS ('datastore ctxsys.default_datastore filter ctxsys.inso_filter sync (on commit)')

       

      However, the query using 'Contains' does not return any search results.

       

      select employee_number,Score(1) from resume_details where CONTAINS(RESUME,'JAVA',1)>0 order by score(1) desc

       

      On checking the  'ctx_user_index_errors' view , it has an entry for every row in the table. However, it does not show any error in the err_text column

       

      ERR_INDEX_NAMEERR_TIMESTAMPERR_TEXTKEYERR_TEXT
      IND_RESUME_DETAILS5/8/2014 18:27AAAl0GAAGAAEFoWAAC
      IND_RESUME_DETAILS5/8/2014 18:27AAAl0GAAGAAEFoWAAD
      IND_RESUME_DETAILS5/8/2014 18:27AAAl0GAAGAAEFoWAAE

       

      Even the index status shows 'VALID'.

       

      I tried dropping and recreating indexes, dropping and recreating the table.. However, it's not working at all.

       

      What's surprising is,it used to work earlier without any problems.

       

      I also have another context index on  another column on the same  table which is a varchar2 column. The search on that column works absolutely fine.

       

      The database version is

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

       

      Request someone in helping  me root cause this issue. Any help is appreciated.

       

       

      Thanks