Wednesday, 27 August 2008

Oracle Text in Five Short Steps

Almost every application I create contains a BLOB (file) table for storing files available for a download. In the most of the cases it will store some documentation related to the application - user manuals, howto's and similar stuff. The download functionality is a nice thing but how about searching the content of those files? There are some examples arround showing how to do that in ApEx but in my opinion the most of them are far to complex. I created an example in my Demo Application, showing how to do that in less than five minutes:

This example includes the most frequently used functionalities:

1. mixed case search

2. highlighted snippets

3. index synchronization on commit

If you need more, you can always look into the Oracle Text Documentation.



Tony said...

This is a great post. Very simple and straight forward and really helpful.

I am curious why you need to create a lexar, I understood a "generic" oracle text index to use mixed case anyway.

Denes Kubicek said...


Thanks. As far as the standard is concerned, I think that it is case sensitive.

Denes Kubicek

Simakas said...

in CTX_DOC.SNIPPET(...,textkey,...) or the second parameter in your code is ROWID, i get DRG-50857: oracle error in drvdoc.get_rowid, unless i use table primary key column (like
thanks for the code! said...

i like this

Anonymous said...


can you send source code of blob search appliction for Oracle application express

Denes Kubicek said...


I am not sending the source code to anyone since it would be too much work. If you follow the instructions on the login page of my application, you will be able to export it yourself.

Denes Kubicek