1

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 :

  1. created a CONTEXT type index
CREATE INDEX dm_document_idx ON DM_DOCUMENT(DOCUMENT) INDEXTYPE IS CTXSYS.CONTEXT;
  1. 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 :

Oracle Fuzzy text search

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • 1
    The documentation is not very clear about this, but `fuzzy` only takes ONE WORD for the first argument - with no spaces. [See this similar question](https://stackoverflow.com/questions/66042362/oracle-fuzzy-search-with-spaces) for a more detailed response. – kfinity Mar 08 '21 at 19:32
  • 1
    For your broader question, Oracle Text is complicated, and I don't find it very helpful as a general-purpose search engine. But [query relaxation](https://docs.oracle.com/database/121/CCAPP/GUID-7DD2AF6B-88FD-40B7-A522-3F59309D3B35.htm#CCAPP9166) does help it act more like one. – kfinity Mar 08 '21 at 19:38
  • Thank you, i'll be sure to update again later when i successfully/unsuccessfully tried this. – Ristyo Arditto Mar 09 '21 at 04:22

0 Answers0