Home » Server Options » Text & interMedia » How to pass the value to a contains query using a parameter (10g)
How to pass the value to a contains query using a parameter [message #343277] Tue, 26 August 2008 17:44 Go to next message
formsdev
Messages: 6
Registered: June 2005
Junior Member
Hi,
I am using the fuzzy operator. Here is my query which gives me correct results.

select * from detinfo_fuzzy_tbl
where CONTAINS(firstname, 'fuzzy(Sam, 60, 1, weight)')>0;

This returns the rows
Sam John
Sam Wayne
Sam Ko

I would like to pass Sam as a parameter value instead of hardcoding sam. How do I do that ?

I put this in a procedure as follows.

PROCEDURE FindDetInfo(pFirstname in varchar2,pLastname in varchar2) IS
c number;
n number;
v_indexscript varchar2(500);
v_count number ;
v_test varchar2(64);

cursor c1 is select * from detinfo_fuzzy_tbl
where CONTAINS(firstname, 'fuzzy(pFirstname, 60, 1, weight)')>0;


BEGIN
For Rec in c1 Loop
dbms_output.put_line(rec.id);
Insert into tab
( ......... end;



My cursor doesnthave any rows.
Also I noticed that if I changed the firstname of one of the rows to pFirstname it takes it.

So basically how can we pass the value to the fuzzy operator instead of hard coding it?

thank you


Re: How to pass the value to a contains query using a parameter [message #343321 is a reply to message #343277] Tue, 26 August 2008 23:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE detinfo_fuzzy_tbl
  2    (id	   NUMBER,
  3  	firstname  VARCHAR2 (15),
  4  	lastname   VARCHAR2 (15))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO detinfo_fuzzy_tbl VALUES (1, 'Sam', 'John')
  3  INTO detinfo_fuzzy_tbl VALUES (2, 'Sam', 'Wayne')
  4  INTO detinfo_fuzzy_tbl VALUES (3, 'Sam', 'Ko')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> CREATE INDEX your_index ON detinfo_fuzzy_tbl (firstname)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE FindDetInfo
  2    (pFirstname in varchar2)
  3  IS
  4    c	      number;
  5    n	      number;
  6    v_indexscript  varchar2(500);
  7    v_count	      number ;
  8    v_test	      varchar2(64);
  9  
 10    cursor c1 is select * from detinfo_fuzzy_tbl
 11    where CONTAINS(firstname, 'fuzzy(' || pFirstname || ', 60, 1, weight)')>0;
 12  
 13  BEGIN
 14    For Rec in c1 Loop
 15  	 dbms_output.put_line(rec.id);
 16  	 dbms_output.put_line(rec.firstname);
 17  	 dbms_output.put_line(rec.lastname);
 18  	 dbms_output.put_line('------------');
 19    END LOOP;
 20  END FindDetInfo;
 21  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC FindDetInfo ('Sam')
1
Sam
John
------------
2
Sam
Wayne
------------
3
Sam
Ko
------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: How to pass the value to a contains query using a parameter [message #344451 is a reply to message #343321] Fri, 29 August 2008 17:09 Go to previous message
formsdev
Messages: 6
Registered: June 2005
Junior Member
Thank you Barbara,
It was exactly what I was looking for .
I tried it and it works.

Thanks for your guidance.

Previous Topic: Multi Language in Oracle Text
Next Topic: Oracle Text added column name to tokens
Goto Forum:
  


Current Time: Thu Mar 28 18:55:42 CDT 2024