Complex Text Query on a Composite Domain Index with "Filter By" and "Order By" Fails with ORA-29902 (Doc ID 1086373.1)

Last updated on APRIL 26, 2012

Applies to:

Oracle Text - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
***Checked for relevance on 27-Apr-2012***

Symptoms

A complex query on an Composite Domain Index with FILTER BY or ORDER BY fails with an empty Oracle Text error:

select top_n_table.query_id, top_n_table.seite_id, top_n_table.myrowid, top_n_table.score_1,
top_n_table.sei_veroeff_id, top_n_table.vf_myrowid, rownum + 0 as idx
from (
select /*+ NOPARALLEL(b) NOPARALLEL_INDEX(b) NOPARALLEL(b_vf) NOPARALLEL_INDEX(b_vf) */
0 as query_id, b.seite_id, b.rowid as myrowid, score(1) as score_1, b_vf.sei_veroeff_id, b_vf.rowid as vf_myrowid
from seite b, seite_veroeff b_vf
where b.seite_id = b_vf.seite_id
and (contains(CTXALL, '((KANN))', 1) > 0)
and (b.veroeff_ts between to_date('1.1.2000 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
and to_date('23.2.2010 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))
and ((b.imp_mandant in (74,75,76,77,94)))
and (b_vf.klas_ausgabe = 668 )
and (b_vf.klas_zeitung = 1 )
and b_vf.seitenummer = 3
and (b.sperre_flag = 'F')
order by score(1) desc
) top_n_table
where top_n_table.seite_id = 830526
/

from xseite b, xseite_veroeff b_vf
*
ERROR at line 6:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms