Search results are easier to scan when they show where the words matched. Oracle Text's CTX_DOC package does that: SNIPPET returns a short extract around the best match with the search words tagged, and MARKUP returns the whole document with every match marked, using tags you choose.
Code for This Guide
The main examples are in the examples/pkg-vector-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
ctx_doc.snippet(index_name, textkey, text_query
[, starttag => '<b>', endtag => '</b>']) return varchar2
ctx_doc.markup(index_name => ..., textkey => ..., text_query => ...,
restab => clob_out, plaintext => true,
starttag => '...', endtag => '...');TEXTKEY is the primary key of the row, passed as a string.
The examples use the TRAVEL_NOTES table and its index from How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX.
Snippets and Markup
Example:
select ctx_doc.snippet('TRAVEL_NOTES_CTX', '3', 'lounge') as snippet from dual;
declare
v_marked clob;
begin
ctx_doc.markup(index_name => 'TRAVEL_NOTES_CTX', textkey => '1',
text_query => 'Dubai | Gold', restab => v_marked,
plaintext => true, starttag => '[', endtag => ']');
dbms_output.put_line(v_marked);
end;
/Output:
SNIPPET ________________________________________________________________ Our Singapore <b>lounge</b> serves laksa and has a quiet room The lounge in [Dubai] is open all night; showers are free for [Gold] members. PL/SQL procedure successfully completed.
SNIPPET returns the part of note 3 around lounge, with the word in bold tags, ready for a web page. MARKUP returns all of note 1 with both Dubai and Gold marked in brackets, the tags chosen with STARTTAG and ENDTAG.
Things to Know
- SNIPPET is a function and works in SQL, so a search query can return snippets with its rows.
- Escape the document text for HTML yourself if it can contain markup, and use tags that cannot appear in it.
- The query given to SNIPPET or MARKUP should be the same one the search used, so the highlights match.
Related Guides
Conclusion
CTX_DOC.SNIPPET returns a highlighted extract around the matches, and MARKUP highlights every match in the full document. Use them to show users why each search result matched.
