There are four tricky Oracle Text errors, which are rather simple when you understand them. A quick set of examples are provided below. You can check Chapter 3 for 10g and Chapter 4 for 11g in the Oracle Text Application Developer’s Guide.
1. Failure to synchronize the
CONTAIN function signatures
This error raises an
ORA-29908 exception. The third actual parameter in the
CONTAIN function must correspond to the single actual parameter in the
SCORE function of a query.
The following query would work because the parameters match:
SELECT sample_document_id , SCORE(1) FROM sample_document WHERE CONTAINS(sample_document,'adjoining magic words',1) > 0;
When they don’t match, like this
SELECT sample_document_id , SCORE(1) FROM sample_document WHERE CONTAINS(sample_document,'adjoining magic words',2) > 0;
, SCORE(1) * ERROR at line 2: ORA-29908: missing PRIMARY invocation FOR ancillary operator
You can also raise this exception when you put a
SCORE function in the
SELECT clause and a
MATCHES function in the
WHERE clause. They’re not compatible. The former uses a
CONTEXT search and the latter a
2. A Duplicate Label Error is actually an ambiguously called function
Sometimes you want to evaluate two conditions in the same CLOB column. This requires you provide two CONTAINS function calls in the query. This seems straightforward but there’s a potential catch. You can’t specify the same scoring value without raising an ORA-29907 exception. By the way, you don’t need to include the
SCORE function when you use the
CONTAINS function. The
SCORE is independent until called, when the synchronization between the two can raise an error.
This works fine when they don’t match whether or not you include the
SCORE function calls:
SELECT sample_document_id , SCORE(1) , SCORE(2) FROM sample_document</code><code> WHERE CONTAINS(sample_document,'magic_word',1) > 0 AND CONTAINS(sample_document,'magic_other_word',2) > 0;
When they do match, like this whether or not you include the
SCORE function calls:
SELECT sample_document_id , SCORE(1) , SCORE(1) FROM sample_document WHERE CONTAINS(sample_document,'magic_word',1) > 0 AND CONTAINS(sample_document,'magic_other_word',1) > 0;
AND CONTAINS(sample_document,'magic_other_word',1) > 0 * ERROR at line 6: ORA-29907: found duplicate labels IN PRIMARY invocations
This happens because you’re effectively calling the same function twice with the same calling signature. You can’t do that. Why? My guess is that they’re tightly coupled behaviors and part of the “framework” of Oracle Text. Anyway, it doesn’t work but I can’t find it in the documentation. If somebody does know the logic for creating this limitation or where I missed it, please let me know.
CONTAINS function is an expression and requires a comparison operation
The last error is the simplest but annoying if you don’t see it right away. If you include a
CONTAINS function call in the WHERE clause without a comparison operator and number, you’ll raise an
ORA-00920. You can use an equality or inequality operator like this to avoid the exception, as shown in the early examples. If you forget this is what you’ll see:
ERROR at line 4: ORA-00920: invalid relational operator
4. The missing index error.
These occur when you attempt using an operator that is inappropriate for the type of column index. You can’t use the
MATCHES function unless the column index is a
CTXRULE. Attempting the
MATCHES function on another index raises:
ERROR: ORA-20000: Oracle Text error: DRG-10599: COLUMN IS NOT indexed
It may means the column isn’t indexed or that it has the wrong type of index.