Skip to Main Content
  • Questions
  • How to escape Reserved Words in CONTAINS function?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, patricia.

Asked: March 13, 2017 - 8:31 pm UTC

Last updated: January 10, 2019 - 1:18 am UTC

Version: Oracle Database 12c

Viewed 10K+ times! This question is

You Asked

Hi, I need to escape reserved words in contains function when I search into blob column.

Let's suppous we have this simple table:
CREATE TABLE MY_TABLE(
   MY_ID       NOT NULL, 
   MY_BLOB     BLOB NOT NULL,
   CONSTRAINT MY_BLOB CHECK(MY_BLOB IS JSON FORMAT JSON),
   CONSTRAINT "MY_ID" PRIMARY KEY ("MY_ID") 
);


I have created an index for blob column:
CREATE INDEX "MY_BLOB_IDX" ON "MY_TABLE"
(
    "MY_BLOB"
)
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');


Then we insert a three records
1, {"myField": <b>"some text here"</b>}
2, {"myField_1":"text here","myField_2":"other text here","myField_3":<b>"something here"</b>}
3, {"myField": "text here"}


So, I need a partial search with a word/group of character "some" inside of blob column from MY_TABLE

I have this query, where "some" is a reserved word:

(1)
SELECT *
FROM MY_TABLE MT
WHERE CONTAINS(MY.MY_BLOB, '%some%', 1) > 0;


So, I should have record with ID 1 and 2, because both have a word or "some" is part of something.

According the documentation for special characters ( https://docs.oracle.com/database/121/CCREF/cqspcl.htm#CCREF0400 ) I need to use {braces}, so I change the query:

(2)
SELECT *
FROM MY_TABLE MT
WHERE CONTAINS(MY.MY_BLOB, '%{some}%', 1) > 0;


So, if I want to search "text" using same query (1), I got a result, but if I search "some" I don't get any result using queries (1) and (2).

Could you please help me?

Please, let me know if you need more information.

Thanks

PS. I'm using oracle 12c

and Connor said...

By default, we have 'stop lists', ie, words that will not be indexed because they are deemed too common to search on. "some" is one of those words. If you want to include those words, you can create your own stop list which could be empty (ie, everything included) or is just a subset of the standard set. eg

SQL> CREATE TABLE MY_TABLE(
  2     MY_ID       int NOT NULL,
  3     MY_BLOB     CLOB NOT NULL,
  4     CONSTRAINT MY_BLOB CHECK(MY_BLOB IS JSON FORMAT JSON),
  5     CONSTRAINT MY_ID PRIMARY KEY (MY_ID)
  6  );

Table created.

SQL> CREATE INDEX MY_BLOB_IDX ON MY_TABLE
  2  (
  3      MY_BLOB
  4  )
  5  INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL> insert into my_table values (1, '{"myField": "some text here"}');

1 row created.

SQL> insert into my_table values (2, '{"myField_1":"text here","myField_2":"other text here","myField_3":"something here"}');

1 row created.

SQL> insert into my_table values (3, '{"myField": "text here"}');

1 row created.

SQL> SELECT *
  2  FROM MY_TABLE MT
  3  WHERE CONTAINS(MT.MY_BLOB, '%some%', 1) > 0;

SQL> exec ctx_ddl.create_stoplist('keep_all_stopwords', 'BASIC_STOPLIST');

PL/SQL procedure successfully completed.

SQL> drop index MY_BLOB_IDX;

Index dropped.

SQL>
SQL> CREATE INDEX MY_BLOB_IDX ON MY_TABLE
  2  ( MY_BLOB) INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('stoplist keep_all_stopwords SYNC (ON COMMIT)');

Index created.

SQL> SELECT *
  2  FROM MY_TABLE MT
  3  WHERE CONTAINS(MT.MY_BLOB, '%some%', 1) > 0;

     MY_ID MY_BLOB
---------- --------------------------------------------------------------------------------
         1 {"myField": "some text here"}
         2 {"myField_1":"text here","myField_2":"other text here","myField_3":"something he

2 rows selected.

SQL>


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

patricia albornoz, March 27, 2017 - 2:42 pm UTC

Cool!

Last question, so how can I create a stop list?

Thanks
Connor McDonald
March 28, 2017 - 12:53 am UTC

You can see right there in the demo I did

exec ctx_ddl.create_stoplist('keep_all_stopwords', 'BASIC_STOPLIST');

then use

exec ctx_ddl.add_stopword

to build the list to whatever you like.

The docs contain the default stopwords that we use, so you could base a list on that.

Escape reserve word cluster

A Reader, April 27, 2018 - 8:03 pm UTC

Hi
We are using Open Source application component which has schema table creation statement as below.
create table cluster ( x number );
we don't have option to change the code ( to change tablename ).

is there any option to overcome this? using double quotes is an option but that requires again code change... i.e. we need to put double quotes around tablename cluster in app code.

create table "cluster" ( x number);
---Table created.
select * from "cluster";
---no data found
select * from "Cluster";
---no data found
select * from "cLUSTER";
---ORA-00942: table or view does not exist
select * from "CLUSTER";
--ORA-00942: table or view does not exist

Many Thanks
Connor McDonald
May 01, 2018 - 1:35 am UTC

Sorry .... I don't know of any way of avoiding this.

SQL> create table xcluster ( x number );

Table created.

SQL> create synonym cluster for xcluster;
create synonym cluster for xcluster
               *
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier


SQL> create view cluster as select * from xcluster;
create view cluster as select * from xcluster
            *
ERROR at line 1:
ORA-00999: invalid view name


Further question on handling errors

Shameen K P, January 07, 2019 - 1:25 pm UTC

Thank you for this and was very useful. I have futher question on handling errors when text based index is used.

1. I have a master table (test_master) which has the master records
2. I have another tabe which has the search data (test_data) for which match (using contains) has to be found from master table.
3. I have to insert matching records to another table (test_result), those records which has only one match in master table
4. I have created a stoplist and text based index.
5. I am getting error (wildcard query expansion resulted in too many terms) for a particular record in test_data table.
6. I am looking for a solution where I can ignore this record (where there are more matches) and get the matching records for other records.

Can you please help by recommending a solution fot this? I have given sample scripts below:
Note: I am aware of wildcard_maxterms attribute, but that does not solve the problem completely.


create table test_master
(
id number,
name varchar2(50)
);

create sequence test_seq start with 1 increment by 1;

-- inserting records to master table
insert into test_master select test_seq.nextval,'abc'||rownum from dual
connect by rownum<=10000;
insert into test_master select test_seq.nextval,'pqr'||rownum from dual
connect by rownum<=10000;
insert into test_master select test_seq.nextval,'xyz'||rownum from dual
connect by rownum<=10000;
insert into test_master select test_seq.nextval,'name'||rownum from dual
connect by rownum<=50000;
commit;



exec ctx_ddl.create_stoplist('keep_all_stopwords', 'BASIC_STOPLIST');

CREATE INDEX idx_test_name ON test_master(name)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('stoplist keep_all_stopwords SYNC (ON COMMIT)');


create table test_data
(
search_name varchar2(50)
);

create table test_result
(
search_name varchar2(50)
);

-- inserting search data
insert into test_data values ('xyz1');
insert into test_data values ('xyz10000');
insert into test_data values ('1');
--delete test_data where search_name='1';
commit;

-- procedure to insert matching records to another table
create or replace procedure testInsert
as
v_errcode NUMBER;
v_errm VARCHAR2(200);
begin
insert into test_result
select a.search_name
from test_data a, test_master b
where contains(b.name, '%' || a.search_name || '%') > 0
group by a.search_name
having count(a.search_name) = 1;
exception
when others then
v_errcode := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('Error code ' || v_errcode || ': ' || v_errm);
end;
/

-- script to exeute proecure
clear screen;
set serveroutput on size 1000000;
set serveroutput on;
exec testInsert;


*************
Error details:

Error code -29902: ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms
********************
Connor McDonald
January 08, 2019 - 1:53 am UTC

To my knowledge, you can't intercept that within the bounds of a single query.

I think you'd need to tackle on a row by row basis, along the lines of:

SQL> create or replace procedure testInsert
  2  as
  3  v_errcode NUMBER;
  4  v_errm VARCHAR2(200);
  5  begin
  6  for a in ( select * from test_data )
  7  loop
  8    DBMS_OUTPUT.PUT_LINE('Loading' || a.search_name);
  9    begin
 10      insert into test_result
 11      select a.search_name
 12      from test_master b
 13      where contains(b.name, '%' || a.search_name || '%') > 0
 14      group by a.search_name
 15      having count(a.search_name) = 1;
 16    exception
 17      when others then
 18         v_errcode := SQLCODE;
 19         v_errm := SUBSTR(SQLERRM, 1, 200);
 20         DBMS_OUTPUT.PUT_LINE('Error code ' || v_errcode || ': ' || v_errm);
 21    end;
 22  end loop;
 23
 24  end;
 25  /

Procedure created.

SQL> exec  testInsert
Loadingxyz1
Loadingxyz10000
Loading1
Error code -29902: ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query
expansion resulted in too many terms

PL/SQL procedure successfully completed.



Then you could possibly consider just loading only those rows when you get a single hit, *without* counting all of the results, eg

SQL> create or replace procedure testInsert
  2  as
  3  v_errcode NUMBER;
  4  v_errm VARCHAR2(200);
  5  begin
  6  for a in ( select * from test_data )
  7  loop
  8    DBMS_OUTPUT.PUT_LINE('Loading' || a.search_name);
  9    begin
 10      insert into test_result
 11      select a.search_name
 12      from dual
 13      where  (
 14        select count(*)
 15        from test_master b
 16        where contains(b.name, '%' || a.search_name || '%') > 0
 17        and rownum <= 2 ) = 1;
 18
 19    exception
 20      when others then
 21         v_errcode := SQLCODE;
 22         v_errm := SUBSTR(SQLERRM, 1, 200);
 23         DBMS_OUTPUT.PUT_LINE('Error code ' || v_errcode || ': ' || v_errm);
 24    end;
 25  end loop;
 26
 27  end;
 28  /

Procedure created.

SQL> exec testInsert
Loadingxyz1
Loadingxyz10000
Loading1
Error code -29902: ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text
error:
DRG-51030: wildcard query expansion resulted in too many terms

PL/SQL procedure successfully completed.


Further question on handling errors

Shameen K P, January 08, 2019 - 6:49 am UTC

Appreciate very quick useful response.
So we have no choice other than looping the records. Was trying to improve the performance by avoiding looping, especially when we have more than 50k records search data.
Connor McDonald
January 10, 2019 - 1:18 am UTC

You could look at bulk-ing up the routine, eg

SQL> create or replace
  2  procedure testInsert as
  3      v_errcode NUMBER;
  4      v_errm VARCHAR2(200);
  5
  6      type row_list is table of varchar2(100) index by pls_integer;
  7      r row_list;
  8
  9    bulk_failed exception;
 10    pragma exception_init(bulk_failed, -24381);
 11
 12    l_cnt int;
 13  begin
 14      select search_name bulk collect into r from test_data;
 15
 16      forall i in 1 .. r.count save exceptions
 17         insert into test_result
 18         select r(i)
 19         from dual
 20         where  (
 21           select count(*)
 22           from test_master b
 23           where contains(b.name, '%' || r(i) || '%') > 0
 24           and rownum <= 2 ) = 1;
 25
 26  exception
 27   when bulk_failed then
 28      l_cnt := sql%bulk_exceptions.count;
 29      for i in 1 .. l_cnt loop
 30        dbms_output.put_line('failed: ' || i || ' index: ' || sql%bulk_exceptions(i).error_index ||' : ' || sqlerrm(-sql%bulk_exceptions(i).error_code));
 31      end loop;
 32  end;
 33  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec testInsert
failed: 1 index: 3 : ORA-29902: error in executing ODCIIndexStart() routine

PL/SQL procedure successfully completed.


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here