Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help With a Contains - Fuzzy Text Search

742821Dec 23 2009 — edited Dec 23 2009
Greetings all-
I'm using Oracle 10g on a Linux server. I'm running a contains query in a web script using PHP5. My contains basic contains query works, but I'm now trying to expand it to include a Fuzzy search. I'm reading an online doc at: [ http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cqoper.htm#i997330] and an Oracle 10g book, "The complete Reference".

Here is my query in the script. This matches what is in the online doc I mentioned.
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_search, 'fuzzy(?, 70, 6, weight)', 1) > 0
AND CC_ISLIVE = ? ORDER BY CC_TITLE";
$sqlParams = array($word_search,1);

For this, I'm getting the following error:
[nativecode=ORA-01036: illegal variable name/number]"]

For clarity of my sql code. The question marks (?) are parameter-bindings. Each ? is bound to a value in the $sqlParams array. The first ? gets the value of $word_search and the second gets the value of 1. $word_search is the search word a user would enter in the search field on the search form. I need to use param bindings like this because I do not know what the search term(s) are that any user might enter. The use of the 70 and the 6 are explained in the online doc by this line:
This query expands to the first six fuzzy variations of government in the index that have a similarity score over 70.

The Oracle book says to write the query like so.
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_searc, '?$word_search') > 0
AND CC_ISLIVE = 1 ORDER BY CC_TITLE";

If I use param bindings for $word_search and the 1 my query would look like so:
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_searc, '??') > 0
AND CC_ISLIVE = ? ORDER BY CC_TITLE";

How do I escape the leading '?' in the contains '??'

Can anyone tell me what I'm doing wrong? Thanks in advance.
Cheers!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 20 2010
Added on Dec 23 2009
2 comments
1,158 views