I am trying to create a search feature, searching the contents of BLOB's in a table. I've been following this link as a baseline : https://oracle-base.com/articles/9i/full-text-indexing-using-oracle-text-9i
I have the following table, with several rows with BLOB's inside :
SQL> desc dm_document
Name Null? Type
----------------------------------------- -------- ----------------------------
DOCUMENT_NO VARCHAR2(100)
DOCUMENT_TYPE VARCHAR2(100)
DOCUMENT BLOB
FILENAME VARCHAR2(255)
MIMETYPE VARCHAR2(100)
LAST_UPDATE_DATE DATE
HOSTNAME VARCHAR2(255)
URL VARCHAR2(255)
FOLDER_NAME VARCHAR2(255)
FOLDER VARCHAR2(10)
CATEGORYID VARCHAR2(10)
SUBCATEGORYID VARCHAR2(10)
VERSIONID VARCHAR2(10)
APPROVALID VARCHAR2(10)
UPLOAD_TYPE VARCHAR2(50)
What i've done is :
- created a CONTEXT type index
CREATE INDEX dm_document_idx ON DM_DOCUMENT(DOCUMENT) INDEXTYPE IS CTXSYS.CONTEXT;
- After that i query the table using simple searches such as this :
SELECT
SCORE(1) score,
DOCUMENT_NO,
DOCUMENT_TYPE,
FILENAME,
MIMETYPE
FROM DM_DOCUMENT
WHERE CONTAINS(DOCUMENT, 'dokumen', 1) > 0
ORDER BY SCORE(1) DESC;
Which works, and i have integrated this into my Oracle APEX app.
However, the search functionality is quite strict and demands exact words and sentences. I imagine that users would want more leniency in the words they search for. To find a solution i have searched around regarding fuzzy searches, and my search leads to the following links :
https://docs.oracle.com/cd/B13789_01/text.101/b10730/cqoper.htm
Which prompted me to try the following query :
SQL> SELECT
2 SCORE(1) score,
3 DOCUMENT_NO,
4 DOCUMENT_TYPE,
5 FILENAME,
6 MIMETYPE
7 FROM DM_DOCUMENT
8 WHERE CONTAINS(DOCUMENT, 'fuzzy(dokumen inii, 70, 6, weight)', 1) > 0
9 ORDER BY SCORE(1) DESC;
SELECT
*
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 15
I am aware that i might have approached this the wrong way, as the fuzzy function seems to work on text columns rather than BLOB. Is there a proper way to do this (or other approaches)?
Thanks in advance.