I am using the following syntax in performing an Oracle Text search.

CONTAINS(nm_lst, 'fuzzy(THIERRY,40, 2500, weight)', 2) > 0

I tried writing the following java sql:

CONTAINS(nm_lst, 'fuzzy(? ,40, 2500, weight)', 2) > 0

and binding it at runtime like this:
preparedStatement.setString(1, "THIERRY");

However, I am getting SQLExceptions in doing this? Can you give me a solution for this?

Thanks Tom. Exactly what I needed.

Below are two different queries that do not bring back consistent results (the way I see it), and wanted to check with you to see if there is a reason for it.

select nm_upr_lst, nm_upr_frst from person where CONTAINS(NM_UPR_LST, ' fuzzy({ADAM}, 68, 6, weight) ', 2) >0;
* This brings back records with the name ADAM as well as variations of the name ADAM. It sounds correct.

select nm_upr_lst, nm_upr_frst from person where CONTAINS(NM_UPR_LST, ' fuzzy({WILL}, 68, 6, weight) ', 2) >0;
* This brings back only variations of the name WILL but not the name WILL itself; unlike in the example above. I know that the name WILL exists, because it comes back when I do a direct lookup.

My Questions:
1. Is there any way I can change the SQL to have the second example work the same as the first?
2. Is this controlled by the Intermedia search parameters. If so, how do I determine what the optimal parameters are for a name search.

WILL is a stopword.

ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2> create table t ( x varchar2(100) );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'Will Adam help them? The Shadow knows....' );

1 row created.

ops$tkyte@ORA9IR2> create index t_idx on t(x) indextype is ctxsys.context;

Index created.

ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
ADAM
HELP
KNOWS
SHADOW
THEM



You can modify the default stop list or create your own (perhaps empty) one to use 

Thanks for the prompt response.

I looked through the default stoplist for the Englist language and all the rest seem legitimate in our case, with the exception of this word. I am better off keeping the rest of the stopwords. I am trying to write a PL/SQL proc to remove the WILL from the default stoplist.

SET SERVEROUTPUT OFF size 100000;
declare
default_stoplist varchar2(100) := CTXSYS.DEFAULT_STOPLIST;
begin
ctx_ddl.remove_stopword( default_stoplist,'will');
end;

Will this work?

The script ran fine and removed the stop word from the default list. I then dropped and created the indexes and it worked fine.

However, I have a question about this. When I created another record with the name 'Will', I ran

Ctx_Ddl.optimize_index('index_name', 'FULL' );

This time, the record did not come back until I actually dropped and recreated all indexes again.

Does this mean that I have to drop and recreate all indexes each time? Or am I doing something incorrectly?

I figured out my error. I should have been doing a sync instead of an optimize. It works fine now!

Thanks!

This is great info. I searched and tried for hours to find a way to use bind variables in the middle of a query that uses Oracle Text Query Rewrite. Using || concatenation characters to build the query was the solution (<textquery>' || :1 || '). This is executed from a Perl application.

SELECT * FROM (
SELECT customer_name, score(1), contact_name, date_opened, tn, domain_name
FROM owner.table_name
WHERE contains(domain_name,'
<QUERY>
<textquery>' || :1 || '
<progression>
<seq><REWRITE>transform((TOKENS, "{" , "}", " " ))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "{" , "}", "AND"))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "{" , "}", "OR" ))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "${", "}", " " ))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "${", "}", "AND"))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "${", "}", "OR" ))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "?{", "}", " " ))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "?{", "}", "AND"))</REWRITE></seq>
<seq><REWRITE>transform((TOKENS, "?{", "}", "OR" ))</REWRITE></seq>
</progression>
</textquery>
</QUERY>
',1 )> 0
ORDER BY score(1) DESC
) WHERE ROWNUM <= :2

Hi tom,
i have this Select Querry in a PL/SQL Procedure, but is not worked, please HELP ME!

select e.DOKUMENT_NR ,
e.ABTEILUNG ,
e.DATUM ,
e.username1,
e.username2,
concat (concat (d.NACHNAME, ' '), d.VORNAME) as Bearbeiter1 ,
concat (concat (r.NACHNAME, ' '), r.VORNAME) as Bearbeiter2 ,
e.ABLAGE,
e.VERTEILER ,
e.SACHE ,
e.SCHLÜSSELWÖRTER ,
e.TYP ,
e.SCHUTZKLASSE ,
be_rotbuch2005.ENTWICKLUNGSBERICHTE_BLOBS.DOKUMENT_TYPE ,
f.Permission
FROM be_rotbuch2005.ENTWICKLUNGSBERICHTE e
FULL OUTER JOIN be_rotbuch2005.ENTWICKLUNGSBERICHTE_BLOBS
ON (e.dokument_nr = be_rotbuch2005.ENTWICKLUNGSBERICHTE_BLOBS.dokument_nr)
FULL OUTER JOIN be_rotbuch2005.ENTWICKLUNGSBERICHTACL f
ON (e.dokument_nr = f.DOCUMENTID)
FULL OUTER JOIN ASPNET.ORA_ASPNET_USERPROFILES d
ON (e.username1 = d.username )
FULL OUTER JOIN ASPNET.ORA_ASPNET_USERPROFILES r
ON (e.username2 = r.username )
where contains (e.SCHLÜSSELWÖRTER, P_Search_String) > 0
or contains (e.sache, P_Search_String) > 0

the error is.

ERROR
ORA-29900: Operator-Bindung ist nicht vorhanden
ORA-06553: PLS-307: Zu viele Deklarationen von 'CONTAINS' entsprechen diesem Aufruf