How to Write Oracle Text CONTAINS Queries (CTX_QUERY)

CONTAINS searches an Oracle Text index with a query language of its own: Boolean operators, proximity, fuzzy matching for misspellings, stemming for word forms, and escapes for special characters. CTX_QUERY adds utilities such as COUNT_HITS, which counts matches without fetching rows.

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

where contains(column, 'text query' [, label]) > 0

word1 AND word2      word1 OR word2      word1 NOT word2
NEAR((w1, w2), n)    fuzzy(word)         $word (stem)      {text} (escape)

ctx_query.count_hits(index_name, text_query, exact => true)

The examples search the TRAVEL_NOTES table and its index from How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX.

Operators in Action

Example:

select note_id, note from travel_notes
where  contains(note, 'lounge AND (Dubai OR Singapore)') > 0 order by note_id;

select note_id, note from travel_notes
where  contains(note, 'NEAR((gold, board), 3)') > 0;             -- within 3 words

select note_id from travel_notes where contains(note, 'fuzzy(passpor)') > 0;  -- misspelled
select note_id from travel_notes where contains(note, '$travels') > 0;          -- stems
select note_id from travel_notes where contains(note, '{wi-fi}') > 0;   -- - alone is MINUS

select ctx_query.count_hits('TRAVEL_NOTES_CTX', 'free', exact => true) as hits from dual;

Output:

   NOTE_ID NOTE
__________ ____________________________________________________________________________
         1 The lounge in Dubai is open all night; showers are free for Gold members.
         3 Our Singapore lounge serves laksa and has a quiet room for sleeping.

   NOTE_ID NOTE
__________ ______________________________________________________________________
         5 Gold and Platinum members board first and may bring two cabin bags.

   NOTE_ID
__________
         6

   NOTE_ID
__________
         4

   NOTE_ID
__________
         2

   HITS
_______
      2

• lounge AND (Dubai OR Singapore) finds notes 1 and 3.
• NEAR finds gold and board within three words of each other, in note 5.
• fuzzy(passpor) finds passport despite the missing letter.
• $travels matches the stem, travel, in note 4.
• Braces escape wi-fi, since a hyphen alone means MINUS in a query.
• COUNT_HITS counts the two notes containing free.

Things to Know

• Common words such as the and in are stopwords and are not indexed.
• Pass user input to CONTAINS through a bind variable, and escape it with braces so operators in it are taken literally.
• SCORE with the same label as CONTAINS ranks results by relevance.

Related Guides

• How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX
• How to Compare Strings with UTL_MATCH (edit distance, Jaro-Winkler)

Conclusion

Oracle Text CONTAINS queries combine Boolean, proximity, fuzzy, and stem operators to find words the way people search. Escape special characters, bind user input, and count matches with CTX_QUERY.COUNT_HITS.