Home » Server Options » Text & interMedia » Oracle text error
Oracle text error [message #172636] Wed, 17 May 2006 07:59 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
I gets the result on the below query

select * from X
WHERE contains(clob_data, '(((($Edu&Pov) OR ($Edu&Pov))))') > 0
and rownum < 10;

But if I execute the query select * from x
WHERE contains(clob_data, '(((($Edu &) OR ($Edu &amp;))))') > 0
and rownum < 10;
, then it gives the oracle text error
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 17



Here is the detail of my oracle enviornment

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production


Thanks in Advance.
Re: Oracle text error [message #172637 is a reply to message #172636] Wed, 17 May 2006 08:05 Go to previous messageGo to next message
Art Metzer
Messages: 2477
Registered: December 2002
Senior Member
Read this AskTom thread, "Intermedia Error".
Re: Oracle text error [message #172794 is a reply to message #172636] Thu, 18 May 2006 01:56 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Thanks for the link. But perhaps this does not solve my purpose. I do not have any issue with the OR clause.

Even the query below gives the error. The error comes when there is any search where "&" is preceded or followed by none.

select * from X
WHERE contains(clob_data, '($Education &amp;)') > 0
AND rownum < 10


select * from X
WHERE contains(clob_data, 'Education&') > 0
AND rownum < 10

select * from X
WHERE contains(clob_data, '&Education') > 0
AND rownum < 10
Re: Oracle text error [message #172901 is a reply to message #172794] Thu, 18 May 2006 09:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
Similar to the OR the & has a special purpose. & is the same as AND. It expects a value before and after. If one or the other is missing, then you get an error.
Re: Oracle text error [message #173036 is a reply to message #172636] Fri, 19 May 2006 01:43 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Thank you Barabara.

My concern is to search anything that starts with "Education & "

If I search like

select * from X WHERE contains(Clob_Data, '($Education) {&} ') > 0 and rownum < 10

then it searches anything that has Education and "&" is neglected. Is it because "&" is in the stopword list. Any way to search atrribute like 'Education & %' ? May I be guided how to know the stop word list.

Also I tried as below to escape the &

select * from X WHERE contains(Clob_Data, 'Education & {&}') > 0 AND rownum < 10 but the result is the same


----------------------------------------------
Now I have a second doubt How can I search anything that starts with Education & Poverty.

When I search like "($Education) & {&} & ($Poverty) or with ($Education) & ($Poverty)", then it gets the data even the text content is "edcation is linked with poverty"

I know I am confusing more but I have tried my best to explain.

Thank you
Ajendra
Re: Oracle text error [message #173170 is a reply to message #173036] Fri, 19 May 2006 19:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
Set the & as a printjoin, so that it gets tokenized and becomes searchable, as demostrated below.

SCOTT@10gXE> CREATE TABLE x (clob_data CLOB)
  2  /

Table created.

SCOTT@10gXE> SET DEFINE OFF SCAN OFF
SCOTT@10gXE> INSERT ALL
  2  INTO x (clob_data) VALUES ('Education')
  3  INTO x (clob_data) VALUES ('Education & Poverty')
  4  INTO x (clob_data) VALUES ('Education and Poverty')
  5  INTO x (clob_data) VALUES ('Education is linked with poverty')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@10gXE> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
  3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '&');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@10gXE> CREATE INDEX x_idx ON x (clob_data)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('LEXER test_lex')
  4  /

Index created.

SCOTT@10gXE> SELECT token_text FROM dr$x_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
&
EDUCATION
LINKED
POVERTY

SCOTT@10gXE> SELECT * FROM x
  2  WHERE  CONTAINS (clob_data, 'Education {&}') > 0
  3  /

CLOB_DATA
--------------------------------------------------------------------------------
Education & Poverty

SCOTT@10gXE> 








Re: Oracle text error [message #173463 is a reply to message #172636] Mon, 22 May 2006 10:48 Go to previous message
Aju
Messages: 94
Registered: October 2004
Member
Thanks Barbara
Previous Topic: SOUNDEX function in Oracle 10g
Next Topic: Extracting snippets from PDF stored in BFILE
Goto Forum:
  


Current Time: Thu Apr 24 13:48:34 CDT 2014

Total time taken to generate the page: 0.14215 seconds