Databases Reference
In-Depth Information
When we want to see a part of the text where our search keyword is located, we can
use a function from the
ctx_doc
package to do this. On lines 2 through 5, we have
used this function. The irst argument for this function is the name of the Oracle Text
Index that we created earlier. The second argument is the primary key column of
the
documents
table. The third argument is the search criteria used to extract a part
of the text. As you can see in the output (in the column
SNIPPET
), the word we were
looking for is in html tags
(<b>Express</b>
). If you don't like these tags, you can
overrule them in the
snippet
function.
SQL> select doc.mimetype
2 , ctx_doc.snippet ('doc_index'
3 ,id
4 ,'express'
5 ,'<span style="color:blue">'
6 ,'</span>'
7 ) snippet
8 from documents doc
9 where contains(doc.document, 'express') > 0
10 /
MIMETYPE SNIPPET
---------------------- --------------------------------------
application/msword Application <span style="color:blue">E
xpress</span>. And that is true up to
a certain point. You probably know tha
t the Oracle Application <span style="
color:blue">Express</span> engine is
When you use the preceding query in a report, make sure to change the
Column
attribute of the
snippet
column to
Standard Report Column
, so that you can see
the HTML markup in the way it is supposed to be.
For this example, we hard coded the
search
keyword, which you wouldn't do in a
real world application, of course. There you would use bind variables to support this
functionality. If you happen to get
ORA-0600
errors when using
ctx_doc.snippet
along with bind variables, you may encounter a bug—
number 5476507
. When this
bug is solved, you may have to patch your database or upgrade to a newer version.
This was just a quick introduction to the wonderful functionality of Oracle Text, and
there is a lot more to explore. The Oracle documentation on Oracle Text is the best
place to start your investigation.
