CODEWITHSUNDEEP
sqloracle-databasefull-text-searchoracle-text
Farrukh Ahmed
Farrukh Ahmed

Reputation: 493

Using variable in Oracle function

I have a variable and want to use in a query inside fuzzy function but it is giving me some syntax error or wrong result considering the var.

ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 21 29902.
00000 - "error in executing ODCIIndexStart() routine"

When I replace the my_var variable in the fuzzy function with some static string it works fine but with variable it is giving me this error.

My query is as follows:

DEFINE my_var = 'Bhularam'; 

SELECT a.EXTERNALID_ENC,
     a.EXTERNALID,
     a.TELNUMBER,
     a.TELAREACODE,
     a.DQ_ENGLISH_NAME,
     a.DQ_ARABIC_NAME,
     a.NAMEFIELD_1,
     a.USAGETYPE,
     a.MANUAL_UPDATE_FLAG,
     a.RULE_UPDATE_FLAG,
     a.BUSINESS_UPDATE_FLAG,
     a.EXCEL_UPDATE_FLAG         
 FROM ( 
   SELECT * FROM (
      SELECT dqlist.*,
             score(1) AS rank
      FROM dq_list_hash_full dqlist
      WHERE contains(dqlist.dq_english_name
                                ,'definescore(fuzzy(my_var, 1, 6, weight),relevance)',1) > 0
     UNION
     SELECT
         dqlist.*,
         score(1) AS rank
     FROM
         dq_list_hash_full dqlist
     WHERE
         contains(dqlist.dq_english_name,'!Bhularam',1) > 0
 )
ORDER BY
 rank DESC
     ) a

I know it is something really stupid but I am unable to get my head around it probably I am new to oracle. Please help me out.

Upvotes: 1

Views: 148

Answers (1)

John A
John A

Reputation: 81

If using sqlplus, verify what prefix character is used to identify substitution variables. Default is set to '&'.

sqlplus > show define
define "&" (hex 26)

Try using your substitution variable within your query, for example 

sqlplus > define my_var='hello world!'
sqlplus > select '&my_var' from dual;
old   1: select '&my_var' from dual
new   1: select 'hello world!' from dual

'HELLOWORLD!'
--------------------------------
hello world!

For your query try (assuming define is set to '&'):

'definescore(fuzzy(&my_var, 1, 6, weight),relevance)',1)

Upvotes: 1

Related Questions