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
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>