Error In Executing Odciindexstart Routine Ora 20000
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog ora-20000 oracle text error drg-50901 text query parser syntax error
ORA-29902: Error in ExecutingOra-06512: At Line 333
ODCIIndexStart() Routine Oracle Database Tips by Burleson Consulting May 1, 2016 Question: We have been getting ORA-29902 errors from certain ora-13208 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.assetidDrg-51030: Wildcard Query Expansion Resulted In Too Many Terms
= 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', examine the error messages produced by the indextype code and take appropriate action. 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 b2007 - 11:48 am UTC Category: SQL*Plus – Version: 8.1.7 Latest Followup You Asked Hi Tom, I was trying following query
Ora-20000 Oracle Text Error Drg-50857
for intermedia based search and results were absolutely shocking. SELECT ID,
Ora-29904
NAME FROM TEST WHERE ID IS NOT NULL AND (( (contains(BODY,' OREGON ' )>0) OR (contains(BODY,' ora 29855 error occurred in the execution of odciindexcreate routine OR ')>0)) ) / Above query ends the current DB session after returning end of file communication channel error. Another query SELECT ID, TITLE FROM TEST http://www.dba-oracle.com/t_ora_29902_error_in_executing_odciindexstart_routine.htm WHERE ID IS NOT NULL AND (contains(BODY,' OR ')>0)) ) / Returns following error SELECT * ERROR at line 1: ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: interMedia Text error: DRG-50901: text query parser syntax error on line 1, column 2 Plz explain. Regards Yogesh and we said... Well, for the EOF on communication https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2886801089883 channel, I'll have to refer you to support. It seems to be a bug dealing with your invalid query you used. There may be a patch for it. For the second query with just 'OR' -- you need to read about the query language behind interMedia. OR is a keyword in the search string itself. You need to escape it. So the search would look like this: ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( id number, name varchar2(80), body varchar2(4000) ); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'a', 'oregon' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'b', 'or' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 'c', 'hello' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> create index search_idx 2 on t(body) 3 indextype is ctxsys.context 4 / Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t 2 where ID IS NOT NULL AND (( (contains(BODY,' OREGON ' )>0) OR (contains(BODY,' {OR} ')>0))
Member No.: 13,502 We have been getting ORA-29902 errors from certain queries against some Oracle 9.2.0.6 databases with Oracle Text. A http://dbaforums.org/oracle/index.php?showtopic=9786 good example of our queries is shown as follows:SELECT r.id, r.parentid, r.nameFROM 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 http://www.aspmessageboard.com/showthread.php?154800-ORA-29902-error-in-executing-ODCIIndexStart() message is shown as follows:11/16/07 01:19:35 ORA-29902: error in executing ODCIIndexStart() routineORA-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 that1. The ora-20000 oracle 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;2. The error happens no matter what that 'anyword' is;3. Dynamic parameter binding for the parameter in the non-Oracle-Text constraint, r.parentid = '{583503FF-67E7-431E-93F8-382CCA65F9C5}', does NOT incur any error;4. The error always happens to some Oracle database ora-20000 oracle text 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?Your any help is greatly appreciated! chandra_vivek View Member Profile Feb 9 2008, 07:02 AM Post #2 Member Group: Members Posts: 14 Joined: 30-March 06 Member No.: 3,961 QUOTE (spiff @ Jan 30 2008, 10:18 PM) 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.nameFROM 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() routineORA-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 that1. 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;2. The error hap
error in executing ODCIIndexStart() If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 6 of 6 Thread: ORA-29902: error in executing ODCIIndexStart() Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-10-200307:22 AM #1 shoby View Profile View Forum Posts Senior Member Join Date Dec 1969 Posts 188 ORA-29902: error in executing ODCIIndexStart() I get this error when running this query ( I have deleted some where clauses from the query for simplicity) The problem is with CONTAINS in the query. ResumeRTF is a clob field. I am looking for words 'electrical engineer or autocad or acad in the resume.
Any suggestions?
SQL = "SELECT Candidates.CandidateID
FROM Candidates, Users, Offices, CandidatePhones MainPhone, CandidatePhones HomePhone, CandidatePhones MobilePhone WHERE Candidates.RecruiterID = Users.UserID (+) AND HomePhone.TypeID (+) = 2 AND Candidates.OfficeID = 41 AND CONTAINS (Candidates.ResumeRTF, ' ( electrical designer & & ( autocad | acad | ) ) ', 1) > 0 ORDER BY upper(Candidates.LastName),upper(candidates.firstn ame) DESC Reply With Quote 03-10-200307:47 AM #2 Pete C. View Profile View Forum Posts Senior Member Join Date Dec 1969 Posts 3,195 My first question is have you tried executing this directly against the Oracle db using SQL*Plus? If not, give it a try. ; )
Next, I'll ask what the ",1" in there is for? Also, why the two "&" symbols. Just for the fun of it try the following:
SELECT Candidates.CandidateID
FROM Candidates, Users, Offices, CandidatePhones MainPhone, CandidatePhones HomePhone, CandidatePhones MobilePhone WHERE Candidates.RecruiterID = Users.UserID (+) AND HomePhone.TypeID (+) = 2 AND Candidates.OfficeID = 41 AND CONTAINS (Candidates.ResumeRTF, ' electrical designer & ( autocad | acad | ) ) > 0 ORDER BY upper(Candidates.LastName),upper(candidates.firstn ame) DESC
I think it is mos