| How to escape reserved words returned in column values as parameters to CONTAINS function [message #670323] |
Wed, 27 June 2018 21:52  |
 |
vka2b
Messages: 21
Registered: June 2018
|
Junior Member |
|
|
Hello,
I have been trying to work with ORACLE TEXT, and am doing something very similar to what was contained in a thread I saw on this forum from a number of years ago (entitled "Matching query"). In that thread, Barbara Boehemer (who seems to be the celebrity around here) posted the following example of using SCORE and CONTAINS:
SCOTT@orcl_11gR2> SELECT SCORE(1), user_desc, emp_name
2 FROM emp_master, menu_user_d
3 WHERE CONTAINS (emp_name, '?' || REPLACE (user_desc, ' ', ',?'), 1) > 0
4 ORDER BY SCORE(1) DESC
5 /
SCORE(1) USER_DESC EMP_NAME
---------- ------------- -------------
4 Wajahat Wajahat
4 Imd Imad El Kane
2 Mohammed Arif Md.Arif
3 rows selected.
The issue I am facing using the above syntax as an example is if menu_user_d.user_desc returns a value with a reserved word (e.g. "Wajahat OR"). I know how to escape reserved words if explicitly stating them within the function by using curly braces (e.g. CONTAINS (emp_name, '?' || REPLACE ({OR}, ' ', ',?'), 1) > 0)), but I can't seem to figure out how to manage reserved words if they are passed to CONTAINS in the value returned from a column referenced in the CONTAINS function. The error I receive is as follows:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 2
29902. 00000 - "error in executing ODCIIndexStart() routine"
*Cause: The execution of ODCIIndexStart routine caused an error.
*Action: Examine the error messages produced by the indextype code and
take appropriate action.
I thought perhaps I could manage it through stoplists, but it turns out that "OR" is already contained in the default stoplist, and it therefore should have been ignored when I created an index against the table in question. I can't seem to figure this out -- any advice anybody can give me would be greatly appreciated. Thank you very much.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
