RE: Oracle text lookup...

From: Ray Feighery <ray.feighery_at_seertechsolutions.com>
Date: Wed, 8 Nov 2006 13:41:42 +1100
Message-ID: <20061108134142143.00000002216@Ray>

Robert

To answer your initial question, I think you can get what you want by not including the %. "ward" will not resolve to "forward"

So you the results you require can be returned by just using ' ward' or 'ward':

SQL> create table ORG_NAME (NAME varchar2(30));

Table created.

SQL>
SQL> insert into ORG_NAME values('my ward');

1 row created.

SQL> insert into ORG_NAME values('your ward');

1 row created.

SQL> insert into ORG_NAME values('his ward');

1 row created.

SQL> insert into ORG_NAME values('backward');

1 row created.

SQL> insert into ORG_NAME values('frontward');

1 row created.

SQL> insert into ORG_NAME values('forward');

1 row created.

SQL> insert into ORG_NAME values('back ward');

1 row created.

SQL> insert into ORG_NAME values('front ward');

1 row created.

SQL> insert into ORG_NAME values('for ward');

1 row created.

SQL> insert into ORG_NAME values('ward ward');

1 row created.

SQL> insert into ORG_NAME values('wood ward');

1 row created.

SQL> insert into ORG_NAME values('woodward');

1 row created.

SQL> insert into ORG_NAME values('ward away');

1 row created.

SQL> insert into ORG_NAME values('walking around the ward');

1 row created.

SQL> insert into ORG_NAME values('ward');

1 row created.

SQL> insert into ORG_NAME values(' ward');

1 row created.

SQL> insert into ORG_NAME select object_name from all_objects;

41113 rows created.

SQL> create index ORG_NAME_NAME on ORG_NAME(NAME) INDEXTYPE IS ctxsys.context;

Index created.

SQL>

SQL> select      count(NAME)
  2  from        ORG_NAME
  3  where       contains(NAME, '% ward') > 0;

select count(NAME)
*
ERROR at line 1:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms


SQL>
SQL> select      NAME
  2  from        ORG_NAME
  3  where       contains(NAME, ' ward') > 0;

NAME

---

my ward
your ward
his ward
back ward
front ward
for ward
ward ward
wood ward
ward away
walking around the ward
ward
 ward

12 rows selected.

SQL>

SQL> select      NAME
  2  from        ORG_NAME
  3  where       contains(NAME, 'ward') > 0;

NAME

---

my ward
your ward
his ward
back ward
front ward
for ward
ward ward
wood ward
ward away
walking around the ward
ward
 ward

12 rows selected.

Ray

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Freeman Sent: Wednesday, November 08, 2006 9:44 AM To: jkstill_at_gmail.com; wojciech.skrzynecki_at_gmail.com Cc: oracle-l
Subject: Oracle text lookup...

Fellow Oracle types...

So, I have a query like this:

select * from ORG_NAME where contains(NAME, '% ward')
> 0

*
ERROR at line 1:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too

many terms

What I want is to be able to bring back records like:

My ward
your ward

and not bring back records like

backward
frontward

Any ideas on how I do this. Looking at Metalink and Google it looks like the % is just expanding everything...

Robert G. Freeman
Author:
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Features (Oracle Press)
Oracle Replication (Rampant Tech Press)
Mastering Oracle8i (Sybex)
Oracle8 to 8i Upgrade Exam Cram (Coriolis <RIP>) Oracle 7.3 to 8 Upgrade Exam Cram (Coriolis <RIP>)

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 07 2006 - 20:41:42 CST