This discussion is archived
4 Replies Latest reply: Oct 9, 2012 9:28 PM by 949210 RSS

functional invocation error-no joins involved

949210 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.
  • 4. Re: functional invocation error-no joins involved
    949210 Newbie
    Currently Being Moderated
    Okay thanks..

Legend

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