4 Replies Latest reply: Oct 9, 2012 11:28 PM by 949210 RSS

    functional invocation error-no joins involved

    949210
      Windows server 2003
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      --------------------------
      SELECT NAME,EMAIL,ADDRESS1,ADDRESS2,CONTACT_NAME,MOBILE,TELEPHONE
      FROM (SELECT NAME,EMAIL,ADDRESS1,ADDRESS2,CONTACT_NAME,MOBILE,TELEPHONE
      FROM MV_CAT_SEG_REG_PROD WHERE CATSEARCH(CAT_IDS,'1,' ,NULL)>0
      AND ACT_STATUS='Y'
      order by DBMS_RANDOM.value)
      WHERE rownum < 8;
      ORA-20000: Oracle Text error:
      DRG-10849: catsearch does not support functional invocation
      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.
      :
      :
      the same query returns rows in our test environment which is
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      :
      :
      differences between the two environments
      these are the indexes in production environment
      LOOKING4     CAT_IDS_IDX     CAT_IDS     NONUNIQUE     VALID     DOMAIN     N     NO               NO
      LOOKING4     CAT_TYPES_IDX     CAT_TYPES     NONUNIQUE     VALID     DOMAIN     N     NO               NO
      LOOKING4     ACT_STATUS_IDX     ACT_STATUS     NONUNIQUE     VALID     NORMAL     N     NO               NO
      and these are the ones in test environment
      LOOKING4     CAT_IDS_IDX     CAT_IDS     NONUNIQUE     VALID     DOMAIN     N     NO               NO
      LOOKING4     CAT_TYPES_IDX     CAT_TYPES     NONUNIQUE     VALID     DOMAIN     N     NO               NO
      ok, one more difference,,, the test environment is a 32 bit machine and the production environment is a 64 bit
      :
      :
      a bit of background
      i need these 7 records(randomly selected from nearly 200 records each time) to send mail to all the people who access our website
      :
      :
      there is no join involved here thats the little difference
      please let me know if you need some other information
      thanks in advance
      also check
      Re: oracle text error when generating random rows
      thanks

      Edited by: 946207 on Oct 8, 2012 8:25 PM
        • 1. Re: functional invocation error-no joins involved
          Barbara Boehmer
          This is one of the drawbacks of catsearch and the biggest reason for using a context index and contains, instead of a ctxcat index and catsearch. If your structured clause is highly selective, then you are likely to get the error that you are receiving, because the optimizer chooses an unsupported execution plan involving functional invocation. So, whether or not you get the error depends on the data and the query. There is no reliable workaround. Please see the excerpt from the manual below. Oracle has greatly expanded the available features of the context index, so that there is little reason for using a ctxcat index anymore. You can do just about anything with a context index that you could with a ctxcat index.

          http://docs.oracle.com/cd/B19306_01/text.102/b14218/csql.htm#CCREF0103

          "Limitation

          If the optimizer chooses to use the functional query invocation, your query will fail. The optimizer might choose functional invocation when your structured clause is highly selective."
          • 2. Re: functional invocation error-no joins involved
            949210
            Ok
            how about
            SELECT * FROM
            (
            SELECT NAME,CAT_IDS,ACT_STATUS,ADDRESS1,ADDRESS2,CONTACT_NAME,MOBILE,TELEPHONE
            FROM MV_CAT_SEG_REG_PROD WHERE CATSEARCH(CAT_IDS,'99,' ,NULL)>0
            )
            WHERE ACT_STATUS='Y'and rownum<3
            order by DBMS_RANDOM.value
            ;
            thanks in advance
            • 3. Re: functional invocation error-no joins involved
              Barbara Boehmer
              946207 wrote:
              Ok
              how about
              SELECT * FROM
              (
              SELECT NAME,CAT_IDS,ACT_STATUS,ADDRESS1,ADDRESS2,CONTACT_NAME,MOBILE,TELEPHONE
              FROM MV_CAT_SEG_REG_PROD WHERE CATSEARCH(CAT_IDS,'99,' ,NULL)>0
              )
              WHERE ACT_STATUS='Y'and rownum<3
              order by DBMS_RANDOM.value
              ;
              thanks in advance
              What about it?

              "WHERE ACT_STATUS='Y'" is the structured part of your query. Depending on how much that limits the rows, which is dependent upon the distribution of data, the optimizer may or may not choose functional invocation, which would cause the query to fail. The solution is to use a context index and contains. There is no reliable way to rewrite a query using a ctxcat index and catsearch to prevent it from failing at unpredictable times.

              Additionally, the way that query is written, it will take the first 3 rows returned, then display them in random order. It will not order them randomly, then display the first rows from that randomly ordered set.