| How to pass the value to a contains query using a parameter [message #343277] |
Tue, 26 August 2008 17:44  |
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  |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
