Tuesday, October 11, 2016

OTN Appreciation Day: Oracle Text

For OTN Appreciation Day, I was told that it wouldn't be appropriate to write about my favorite Oracle feature (APEX, obviously).  So I'll gladly promote my second-favorite Oracle Database feature...Oracle Text!

I've used Oracle Text for many years - from when it was SQL*TextRetrieval to Oracle ConText Option to Oracle interMedia Text to finally Oracle Text.  This was one of those products that used to be a for-cost option and was merged into the Oracle Database as native, no-cost functionality (how cool is that?).  You can use Oracle Text to index BLOB columns containing Microsoft Word or PDF documents, you can score the query results for relevance, you can perform a proximity search within the contents (find "Oracle" and "APEX" within 10 words of each other), you can search within sections of a document, you can do a fuzzy search, you can create a thesaurus to assist in searching for similar terms, you can create a text result with the matching words highlighted, and on and on.

The beauty of Oracle Text is that it's all completely accessible in SQL.  Any tool that can "talk" SQL can easily take advantage of this rich functionality in the Oracle Database - Java, .NET, PHP, Node, and of course, APEX!  I authored the PL/SQL functions and text indexes (and text queries) for AskTom back in 2001 - and they're still running as fast as ever today.  One of the most popular applications inside of Oracle, an employee directory (1.5M page views every day from 55,000 distinct users), is an APEX application that we're responsible for - and we are in the process of expanding this to use the fuzzy search capabilities of Oracle Text - what is more commonly misspelled than someone's name?  And it's easy, because this is all running inside the Oracle Database.  Whether your content is a string or BLOB or XML or JSON, once this content is inside the Oracle Database, it's accessible to Oracle Text and SQL, and the application development opportunities on top of this are easy.  I'm a big  fan of Oracle Text, and you should take a look at it too!


Flavio Casetta said...

I am a big fan too, actually years ago I wrote an Apex application to help with the creation of Oracle TEXT indexes and their related entities.



Scott Wesley said...

Fascinating insight, thanks. I use this years ago and I have a feeling a coming project may benefit, so I look forward to playing again.

Once upon a time I did a presentation on it, but I think it was mostly demo