This discussion is archived
2 Replies Latest reply: Aug 15, 2012 1:57 AM by 949210 RSS

oracle text error when generating random rows

949210 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points