Home » Server Options » Text & interMedia » How to escape reserved words returned in column values as parameters to CONTAINS function (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
| 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.
|
|
|
|
| Re: How to escape reserved words returned in column values as parameters to CONTAINS function [message #670329 is a reply to message #670323] |
Thu, 28 June 2018 02:25  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could create a function that loops through the reserved words, putting curly brackets around them, then use that function in your query. I have provided a reproduction of the problem below, followed by a solution using such a function. I have used v$reserved_words which requires select privilege on the underlying v_$reserved words when used in a stored named function or procedure. I have also added, then removed spaces to ensure that that it replaces only the standalone reserved word and not a word with a reserved word nested in it. I also converted to upper case to avoid case issues. You could use a separate table if you like or hard code the words in the function instead, depending on what you want and what you can obtain privileges for.
-- reproduction of problem:
SCOTT@orcl_12.1.0.2.0> create table emp_master (emp_id varchar2(12),emp_name varchar2(30))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into emp_master(emp_id,emp_name) values ('7014','Md.Arif')
3 into emp_master(emp_id,emp_name) values ('6777','Wajahat')
4 into emp_master(emp_id,emp_name) values ('1008','Imad El Kane')
5 select * from dual
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> create table menu_user_d (user_id varchar2(12),user_desc varchar2(30),user_emp_id varchar2(12))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into menu_user_d(user_id,user_desc,user_emp_id) values ('ARIF','Mohammed Arif',null)
3 into menu_user_d(user_id,user_desc,user_emp_id) values ('wajahat','Wajahat Or',null)
4 into menu_user_d(user_id,user_desc,user_emp_id) values ('Imad','Imd',null)
5 select * from dual
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX emp_name_idx ON emp_master (emp_name) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> 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 /
SELECT SCORE(1), user_desc, emp_name
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 11
-- solution:
SCOTT@orcl_12.1.0.2.0> CONNECT SYS AS SYSDBA
Connected.
SYS@orcl_12.1.0.2.0> -- privilege needed to select from v$reserved_words within function:
SYS@orcl_12.1.0.2.0> GRANT SELECT ON v_$reserved_words TO scott
2 /
Grant succeeded.
SYS@orcl_12.1.0.2.0> CONNECT scott/tiger
Connected.
SCOTT@orcl_12.1.0.2.0> -- function to put curly brackets around reserved words:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION escape_reserved_words
2 (p_string IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 v_string VARCHAR2(4000) := ' ' || UPPER (p_string) || ' ';
6 BEGIN
7 FOR i IN (SELECT keyword FROM v$reserved_words) LOOP
8 v_string := REPLACE (v_string, ' ' || i.keyword || ' ', ' {' || i.keyword || '} ');
9 END LOOP;
10 RETURN TRIM (v_string);
11 END escape_reserved_words;
12 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- what the function returns:
SCOTT@orcl_12.1.0.2.0> SELECT escape_reserved_words (user_desc) FROM menu_user_d
2 /
ESCAPE_RESERVED_WORDS(USER_DESC)
--------------------------------------------------------------------------------
MOHAMMED ARIF
WAJAHAT {OR}
IMD
3 rows selected.
SCOTT@orcl_12.1.0.2.0> -- modified query using function:
SCOTT@orcl_12.1.0.2.0> SELECT SCORE(1), user_desc, emp_name
2 FROM emp_master, menu_user_d
3 WHERE CONTAINS (emp_name, '?' || REPLACE (escape_reserved_words (user_desc), ' ', ',?'), 1) > 0
4 ORDER BY SCORE(1) DESC
5 /
SCORE(1) USER_DESC EMP_NAME
---------- ------------------------------ ------------------------------
4 Wajahat Or Wajahat
4 Imd Imad El Kane
2 Mohammed Arif Md.Arif
3 rows selected.
|
|
|
|
| Re: How to escape reserved words returned in column values as parameters to CONTAINS function [message #670330 is a reply to message #670329] |
Thu, 28 June 2018 02:31 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A simpler and perhaps just as effective and probably faster solution is to modify the query to put curly brackets around every word. It looks like ? still works with it, as shown below.
SCOTT@orcl_12.1.0.2.0> 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 Or Wajahat
4 Imd Imad El Kane
2 Mohammed Arif Md.Arif
3 rows selected.
-- This is what '?{' || REPLACE (user_desc, ' ', '},?{') || '}' is producing within the query above:
SCOTT@orcl_12.1.0.2.0> SELECT '?{' || REPLACE (user_desc, ' ', '},?{') || '}' FROM menu_user_d
2 /
'?{'||REPLACE(USER_DESC,'','},?{')||'}'
--------------------------------------------------------------------------------
?{Mohammed},?{Arif}
?{Wajahat},?{Or}
?{Imd}
3 rows selected.
[Updated on: Thu, 28 June 2018 02:36] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 12:06:33 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
