Oracle Text search in Files

Question

I am creating a database that needs to search in text files (.doc, .txt, .pdf, ...). Start creating a preference:

ctx_ddl.create_preference('DOCSPIDER_DIR','FILE_DATASTORE');

I do not assign the 'path' value as there may be subdirectories. Then I create a table:

create table document (id number, path varchar2(2000));
ALTER TABLE document ADD (CONSTRAINT document_pk PRIMARY KEY (ID));

Create the index:

create index document_index on document(path)
indextype is ctxsys.context
parameters ('datastore DOCSPIDER_DIR filter ctxsys.auto_filter');

And the command to sync:

ctx_ddl.sync_index('document_index', '2M');

After the structure is created, I insert a record, pointing to an existing document:

INSERT INTO document VALUES (1, '\\server\oracle_text_files\file_name.txt');

However, when you run a query searching the contents of this document, it does not return data:

SELECT * from document WHERE CONTAINS(path, 'test', 1) > 0;

Something's missing?

Answer 1

I bet you will get results if you go:

SELECT * from document WHERE CONTAINS(path, 'txt', 1) > 0;

Indeed, that INSERT you do isn't going to load the file into the database! You must look into something to load your file as CLOB or something.

There are interesting posts on SO to do the same, say

• Load text files as clob to database