2 Replies Latest reply: Aug 15, 2012 3:57 AM by 949210 RSS

    oracle text error when generating random rows

    949210
      Hello
      firing
      SELECT *
      FROM
      (
      SELECT NAME,EMAIL,ADDRESS1,ADDRESS2,CONTACT_NAME,MOBILE,TELEPHONE FROM MV_CAT_SEG_REG_PROD
      WHERE CATSEARCH(CAT_TYPES,'security services*' ,NULL)>0 AND
      PLAN_ID=1 AND ACT_STATUS='N'
      ORDER BY DBMS_RANDOM.VALUE
      )
      where rownum < 4;


      returns


      ORA-20000: Oracle Text error:
      DRG-10849: catsearch does not support functional invocation
      DRG-10599: column is not indexed
      20000. 00000 - "%s"
      *Cause:    The stored procedure 'raise_application_error'
      was called which causes this error to be generated.
      *Action:   Correct the problem as described in the error message or contact
      the application administrator or DBA for more information.




      using oracle 10gr2 on windows server 2003
      i have tried 1)dropping the index and creating it again, the index type is "CTXSYS"."CTXCAT"
      2) deleting the stats -checking
      3)recreating the stats- checking

      the table here is a materialized view
      i need to tell you people that
      there are two indexes cat_types_ind and cat_ids_idx on cat_types and cat_ids columns respectively
      the inner query uses cat_types_idx index when executed and seen in sqladvisor
      1)removing the order by clause will make the query work but i really want that order by clause
      2)the inner-query-only works fine
      3) i have seen the forums and they have helped regarding the things i tried above but it does not work

      please tell me if i need to further elaborate on anything
      thanks in advance
        • 1. Re: oracle text error when generating random rows
          953710
          I have same problem, my query is:
          SELECT *
          FROM
          (SELECT
          /*+ FIRST_ROWS(50) */
          NTQ.*,
          ROWNUM RNUM1
          FROM
          (SELECT
          /*+ INDEX(DL_TSD_DEFTR_CI) */
          *
          FROM ima_ol.DL_TSD_SITUATION s
          WHERE (CATSEARCH(DEF_TRANS,'milano ',NULL)>0)
          AND (s.FORECAST = 0)
          AND (s.STATE IN (1,0,4))
          AND (s.ARCH_STATE = 0)
          ORDER BY s.VET_TS DESC
          ) NTQ
          WHERE ROWNUM <=50
          )
          WHERE RNUM1 >=1

          my oracle and system version:

          Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
          PL/SQL Release 11.1.0.7.0 - Production
          "CORE     11.1.0.7.0     Production"
          TNS for Solaris: Version 11.1.0.7.0 - Production
          NLSRTL Version 11.1.0.7.0 - Production

          I have suggested that to solve the problem I should alter the statistics of the offending table to force to use this index .. how do I do? thanks in advance
          • 2. Re: oracle text error when generating random rows
            949210
            i hope the problem will not be there in release 2
            did you post it as a new thread 950707??

            Edited by: 946207 on Aug 15, 2012 2:26 PM