OraFAQ Forum: Text & interMedia » Oracle text error

Home » Server Options » Text & interMedia » Oracle text error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle text error [message #172636]

Wed, 17 May 2006 07:59

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 &))))') > 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.

Report message to a moderator

Re: Oracle text error [message #172637 is a reply to message #172636]

Wed, 17 May 2006 08:05

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Read this AskTom thread, "Intermedia Error".

Report message to a moderator

Re: Oracle text error [message #172794 is a reply to message #172636]

Thu, 18 May 2006 01:56

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 &)') > 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

Report message to a moderator

Re: Oracle text error [message #172901 is a reply to message #172794]

Thu, 18 May 2006 09:53

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Re: Oracle text error [message #173036 is a reply to message #172636]

Fri, 19 May 2006 01:43

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

Report message to a moderator

Re: Oracle text error [message #173170 is a reply to message #173036]

Fri, 19 May 2006 19:33

Barbara Boehmer
Messages: 9100
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>

Report message to a moderator

Re: Oracle text error [message #173463 is a reply to message #172636]

Mon, 22 May 2006 10:48

Aju
Messages: 94
Registered: October 2004

Member

Thanks Barbara

Report message to a moderator

Previous Topic:	SOUNDEX function in Oracle 10g
Next Topic:	Extracting snippets from PDF stored in BFILE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 10:22:01 CST 2024