Home » Server Options » Text & interMedia » Oracle Text error (Oracle 10g)
Oracle Text error [message #405992] Mon, 01 June 2009 07:46 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

All,
I have table T1 with column tc1. Tc1 has a domain index on it.

CREATE INDEX t1_ID1 ON T1
(tc1)
INDEXTYPE IS CTXSYS.CONTEXT;


For certain words, if I do a search I get below error:


SQL> SELECT count(*) 
  2    FROM T1 
  3   WHERE contains (tc1, 'NTI') > 0; 
SELECT count(*) 
* 
ERROR at line 1: 
ORA-29902: error in executing ODCIIndexStart() routine 
ORA-20000: Oracle Text error: 
DRG-50901: text query parser syntax error on line 1, column 4   


Any idea?

Thanks,
NavKrish
Re: Oracle Text error [message #406000 is a reply to message #405992] Mon, 01 June 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DRG-50901: text query parser syntax error on line %(1)s, column %(2)s
 *Cause: bad query
 *Action: fix query
 *Mnemonic: PE_SYNTAX

Regards
Michel
Re: Oracle Text error [message #406008 is a reply to message #406000] Mon, 01 June 2009 10:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Any time that you try to search for a word that has special meaning to Oracle Text or a stopword or some such thing, without escaping it, you are likely to get a syntax error. "NTI" has special meaning to Oracle Text, as documented here:

http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cqoper.htm#sthref1132

You can find a list of such words here:

http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cqspcl.htm#sthref1319

So, you need to escape it, as documented here:

http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cqspcl.htm#sthref1309

Please see the demonstration below, that first reproducers the error, then corrects it by using curly brackets to escape the special word.

SCOTT@orcl_11g> CREATE TABLE t1 (tc1 VARCHAR2(30))
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO t1 VALUES ('NTI')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE INDEX t1_id1 ON t1 (tc1) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl_11g> SELECT * FROM t1 WHERE CONTAINS (tc1, 'NTI') > 0
  2  /
SELECT * FROM t1 WHERE CONTAINS (tc1, 'NTI') > 0
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 4


SCOTT@orcl_11g> SELECT * FROM t1 WHERE CONTAINS (tc1, '{NTI}') > 0
  2  /

TC1
------------------------------
NTI

SCOTT@orcl_11g>

[Updated on: Mon, 01 June 2009 10:11]

Report message to a moderator

Re: Oracle Text error [message #406507 is a reply to message #406008] Thu, 04 June 2009 06:00 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Perfect! Thanks Barbara....
Previous Topic: Select all strings that appear a substrings of a given string
Next Topic: Problem using CTXXPATH index
Goto Forum:
  


Current Time: Fri Mar 29 07:28:20 CDT 2024