MacLochlainns Weblog

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 SCORE and 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;

you raise

,                 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 RULE search.

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;

you raise

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.

3. The 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.