Question: We have been getting ORA-29902 errors from certain
queries against some Oracle 9.2.0.6 databases with Oracle Text. A
good example of our queries is shown as follows:
SELECT r.id,
r.parentid, r.name
FROM assetrefs r
INNER JOIN FullTextStrings s ON s.assetid = r.id AND CONTAINS( s.val,
'anyword', 1 ) > 0
WHERE r.parentid = '{583503FF-67E7-431E-93F8-382CCA65F9C5}';
The complete error message is shown as follows:
11/16/07 01:19:35
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1
State:S1000,Native:29902,Origin:[Oracle][ODBC][Ora]
Now, the tricky things about the error are:
-
The error happens ONLY when the parameter for Oracle Text, 'anyword',
is passed in by our application via dynamic parameter binding.
If we don't use dynamic binding for that parameter, the query runs
well and returns correct results.
-
The error happens no matter what 'anyword' is.
-
Dynamic parameter binding for the parameter in the
non-Oracle-Text constraint, r.parentid =
'{583503FF-67E7-431E-93F8-382CCA65F9C5}', does NOT incur any error.
-
The error always happens to some Oracle database instances but
never happened to other instances.
Based on the observation described above, we suspected that our
error problem is related to the configuration and/or environment of
Oracle Text. Does anyone out there have any idea about what's wrong
with the configuration/environment? Any idea how to fix the problem?
Answer: To diagnose any error, you start by using the
oerr utility to display the ORA-29902 error:
ORA-29902: error in
executing ODCIIndexStart() routine
Cause: The execution of ODCIIndexStart routine caused an error.
Action: Examine the error messages produced by the indextype code
and take appropriate action.
In this case, it turns out the DRG-50901 is referencing a syntax error.
The locations in error messages are not always reliable, so reviewing the code
as listed, I noted that 'anyword' is in single quotes when it should be in curly braces as follows:
INNER JOIN
FullTextStrings s ON s.assetid = r.id AND CONTAINS( s.val, '{anyword}',
1 ) > 0
This fix should eliminate the ORA-29902 and the DRG-50901 errors.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
