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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
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

Re: How to escape reserved words returned in column values as parameters to CONTAINS function [message #670370 is a reply to message #670330] Thu, 28 June 2018 18:01 Go to previous message
vka2b
Messages: 21
Registered: June 2018
Junior Member
Thank you so much, Barbara! I used the second solution and it worked like a charm. You live up to your reputation on this forum. Smile
Previous Topic: Need to get the one specific record with 's
Next Topic: Oracle Text Performance
Goto Forum:
  


Current Time: Mon Oct 15 20:13:56 CDT 2018