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 checkRe: oracle text error when generating random rows
thanks

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.

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

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.