Searching the contents of documents

Because we are creating a document management system, there are going to be documents stored inside the database, obviously. Searching through these documents is a must-have feature. Oracle supports this kind of functionality in the form of Oracle Text functionality.

In order to work with the Oracle Text feature, we need a special type of index—a context index—on our documents tables, more specifically on the column that stores the document:

SQL> create index doc_index on documents (document)
  2  indextype is ctxsys.context
  3  parameters ('SYNC (ON COMMIT) TRANSACTIONAL')
  4  /

Index created.

The index type that is needed for Oracle Text is CTXSYS.CONTEXT (line 2). On line 3, we specify that we want this index to be refreshed when a commit is issued.

Note

There are many more options that can be used with Oracle Text, such as searching for alternative spelling, searching for words in a certain context, or searching independent diacritic characters. More...