1 Reply Latest reply: May 23, 2012 4:14 AM by Herald ten Dam RSS

    CONTAINS function problem

    ElMazzaX
      Hi all!!

      i got a problem with contains function. i'm running oracle 11g.

      if i execute this query:


      SELECT x.lid as id,  x.sztitular as titular, e.szname as proName, x.szresumen  as resumen, b.sztitle  as catName, 
      f.szname  as secName, d.szname  as cliName, x.datecreation as datecreation 
      
      FROM CPR_PRACTICAL_CASE x,ctg_category b, CPR_CLIENT d, pro_product e, CAT_SECTOR f, CPR_PCASE_PRODUCT g, CPR_PCASE_SECTOR h, 
      
      PRO_PRODUCTCATEGORY i WHERE  x.lid = g.lpcaseid and e.lid = g.lproductid and x.lid = h.lpcaseid 
      and f.lid = h.lsectorid and x.lclientid = d.lid and i.lproductid = e.lid and  b.lid = i.lcategoryid 
      AND x.szlocale = 'es-ES' AND  x.bavailable = '1'
      i get 1 row as result with the column sztitular = "rodillos de medidas"

      if i only add one more sentece to that query:
      AND CONTAINS( x.sztitular, 'rodillos',1) >0
      the query returns an empty set.. i really don't understand why because the term "rodillos" is present in the row's column called "sztitular"...

      i've put an index on that column:
      create index ITXT_TITULAR on CPR_PRACTICAL_CASE(sztitular) INDEXTYPE IS CTXSYS.CONTEXT;
      any help?

      many thanks!!!!
        • 1. Re: CONTAINS function problem
          Herald ten Dam
          Hi,

          did the row existed before creating the index or did you add the row after creating? You have created the index without a sync option, so a new row will not be reflected in index, only after a sync.
          A quick sync goes with
          begin
            ctx_ddl.sync_index('ITXT_TITULAR' ); 
          end;
          Furthermore you could add a sync to the index creation, after that every commit will also sync the index:
             create index ITXT_TITULAR on CPR_PRACTICAL_CASE(sztitular) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)') ;
          Herald ten Dam
          http://htendam.wordpress.com