Autocomplete helps users find content by suggesting likely matches as they type. Oracle AI Database provides this capability through DBMS_SEARCH, making it useful for search fields, name lookups, product catalogs, and other applications where users benefit from timely suggestions.
In this post, we’ll explain how autocomplete works and build a small example using public Wikipedia data.
How does it work?
DBMS_SEARCH, the package used for Oracle’s ubiquitous search (see Further Reading below) , also provides APIs for autocomplete. The workflow has two main steps:
- Create an autocomplete index. DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX creates an index on a table column or a field in a JSON column. Oracle Text autocomplete settings prepare the data for prefix and completion matching. For example, a title beginning with “Oracle” can be found as a user types “Ora”.
- Query for suggestions. DBMS_SEARCH.AUTOCOMPLETE takes the partial text entered and returns ranked suggestions in a JSON array. Fuzzy matching can also be enabled to find near-misspellings.
A common way to present these suggestions is in a dropdown beneath a search field that updates as the user types.
Example
We’ll use public data from the Wikimedia Foundation’s Wikipedia dumps and store it in a table called wiki_data, with columns for article content, titles, URLs, and IDs. Then we’ll create an autocomplete index on the title column and query it with a bind variable to retrieve suggestions as the user types.
Inspect the example data
Before creating the autocomplete index, let’s inspect the source table. In SQLcl, use INFO WIKI_DATA to display details about the table. INFO is a SQLcl client command, not a SQL statement.
The table contains 25,000 rows, with ID as the primary key and columns for URL, TITLE, and TEXT. The TEXT column is a CLOB, which can store large amounts of character data.
SQL> INFO WIKI_DATA
TABLE: WIKI_DATA
LAST ANALYZED: 2026-10-09
ROWS: 25000
SAMPLE SIZE: 25000
INMEMORY: DISABLED
COLUMNS
NAME DATA TYPE NULL
ID NUMBER No
URL VARCHAR2(200 BYTE) Yes
TITLE VARCHAR2(100 BYTE) Yes
TEXT CLOB Yes
INDEXES
INDEX_NAME UNIQUENESS STATUS COLUMNS
WIKIQUERY.WIKI_ID UNIQUE VALID IDTo view a sample record, query the row whose title is “Albert Einstein.”
SET PAGESIZE 1000
SET LONG 10000
SELECT id, url, text
FROM wiki_data
WHERE title = 'Albert Einstein';The query returns this example:
ID
2138
URL
https://simple.wikipedia.org/wiki/Albert%20Einstein
TEXT
Albert Einstein (14 March 1879 – 18 April 1955) was a German-born American
scientist. He worked on theoretical physics. He developed the theory of
relativity. He received the Nobel Prize in Physics in 1921 for theoretical
physics. His famous equation is ...Create an autocomplete index on column TITLE
Use DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX to create an autocomplete index on a table column or a JSON field. In this example, we’ll index the TITLE column in WIKI_DATA.
You only need to provide the index name, the table name, and the column, see the call below.
Because we omit the optional parameters, Oracle uses their defaults, including the GENERIC domain. For JSON data, you can specify a field_name; other options let you configure the Oracle Text lexer, wordlist, stoplist, or suggestion domain. Use GENERIC for general text completion or NAME for name-style completion (optimized for names).
BEGIN
DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX(
index_name => 'WIKI_AC_IDX',
source_name => 'WIKI_DATA',
column_name => 'TITLE'
);
END;
/After the procedure completes, the infrastructure for Oracle Text is created with the supporting objects in the current schema.
Query autocomplete suggestions
Use DBMS_SEARCH.AUTOCOMPLETE to query the index. It takes the index name, and the characters entered so far and returns a JSON array of ranked suggestions. By default, it returns up to 10 suggestions.
SELECT DBMS_SEARCH.AUTOCOMPLETE(
idx_name => 'WIKI_AC_IDX',
typed_chars => '&prefix'
) AS results;Here, &prefix is a SQLcl/SQL*Plus substitution variable. The client prompts for its value; for example:
Enter value for prefix: Black
old 2: DBMS_SEARCH.AUTOCOMPLETE (idx_name => ‘WIKI_AC_IDX’, typed_chars => ‘&prefix’) results
new 2: DBMS_SEARCH.AUTOCOMPLETE (idx_name => ‘WIKI_AC_IDX’, typed_chars => ‘Black’) results
The returned JSON in this run includes an ID, a DATA value, and a SCORE for each suggestion:
[
{"ID":"AAAS8RAAAAAARJ+AAD","DATA":"Black pudding","SCORE":78},
{"ID":"AAAS8RAAAA...","DATA":"Black","SCORE":78},
...
]The JSON can be easier to read when projected into SQL columns with JSON_TABLE:
SELECT jt.score, jt.data
FROM JSON_TABLE(
DBMS_SEARCH.AUTOCOMPLETE(
idx_name => 'WIKI_AC_IDX',
typed_chars => '&prefix'
),
'$[*]' COLUMNS (
data VARCHAR2(4000) PATH '$.DATA',
score NUMBER PATH '$.SCORE'
)
) jt
ORDER BY jt.score DESC, jt.data;
For this example, TITLE fits in the VARCHAR2(4000) projection, so can be returned directly.
The JSON output shown earlier also includes an ID that, in this result, corresponds to the source row’s ROWID. You can use it to retrieve other columns from WIKI_DATA, such as URL and TEXT, including longer values that may not fit in the DATA projection. To use ID in this same query, add it to the JSON_TABLE column list.
The function also accepts optional parameters. num_results sets the maximum number of suggestions. Set fuzzy to TRUE to include misspellings; the default is FALSE. The domain must match the domain used when creating the index.
DBMS_SEARCH.AUTOCOMPLETE(
idx_name VARCHAR2,
typed_chars VARCHAR2,
num_results NUMBER DEFAULT 10,
fuzzy BOOLEAN DEFAULT FALSE,
domain VARCHAR2 DEFAULT 'GENERIC'
) RETURN JSON;For example, request seven suggestions and enable fuzzy matching to test the misspelled input Blacck:
SELECT jt.score, jt.data
FROM JSON_TABLE(
DBMS_SEARCH.AUTOCOMPLETE(
idx_name => 'WIKI_AC_IDX',
typed_chars => '&prefix',
num_results => 7,
fuzzy => TRUE
),
'$[*]' COLUMNS (
data VARCHAR2(4000) PATH '$.DATA',
score NUMBER PATH '$.SCORE'
)
) jt
ORDER BY jt.score DESC, jt.data;When prompted, enter Blacck.
Enter value for prefix: Blacck
old 5: typed_chars => ‘&prefix’ ,
new 5: typed_chars => ‘Blacck’ ,
The results from this run include:
SCORE DATA
---------- ------------------------------
98 Black pudding
98 Black
98 Black hole
98 Black Forest
98 Black comedy
98 Black Sabbath
98 Black HandWhen should you choose the NAME domain?
Choose the NAME domain when your data contains names and users may misspell them or enter their words in a different order. It uses Oracle Text’s NDATA search under the hood, which applies specialized algorithms for name queries.
In this example, we’ll use NAME to search article titles for a misspelled name entered in a different word order.
First, remove the existing autocomplete index and its associated objects with:
BEGIN
DBMS_SEARCH.DROP_AUTOCOMPLETE_INDEX(
index_name => 'WIKI_AC_IDX',
source_name => 'WIKI_DATA'
);
END;
/Now create the index with the NAME domain:
BEGIN
DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX(
index_name => 'WIKI_AC_IDX',
source_name => 'WIKI_DATA',
column_name => 'TITLE',
domain => 'NAME'
);
END;
/When querying the index, specify the same domain used to create it:
SELECT jt.score, jt.data
FROM JSON_TABLE(
DBMS_SEARCH.AUTOCOMPLETE(
idx_name => 'WIKI_AC_IDX',
typed_chars => '&prefix',
domain => 'NAME'
),
'$[*]' COLUMNS (
data VARCHAR2(4000) PATH '$.DATA',
score NUMBER PATH '$.SCORE'
)
) jt
ORDER BY jt.score DESC;When prompted, enter Munichen Bayern. This input misspells “Munich” and reverses the word order in “Bayern Munich.”
Enter value for prefix: Munichen Bayern
old 5: typed_chars => ‘&prefix’ ,
new 5: typed_chars => ‘Munichen Bayern’ ,
In this run, the top suggestion is FC Bayern Munich, with a score of 86:
SCORE DATA
----- ----------------------------------------
86 FC Bayern Munich
57 Municipalities of the canton of Bern
43 Carole Bayer Sager
43 Munich
43 Munich U-Bahn
43 Bayernliga
43 Munich Airport
38 San Francisco Municipal Railway
38 Municipalities of Republika Srpska
38 Municipality
Under the hood: settings and synchronization
Oracle Text provides dictionary views that help you inspect an index’s settings and synchronization. For example, you can use them to see how the autocomplete index is maintained when data in the base table changes.
To check the number of indexed documents, the index status, and its maintenance mode, query CTX_INDEXES and filter by the index owner and name:
SELECT idx_docid_count,
idx_status,
idx_maintenance_type AS maintenance
FROM ctxsys.ctx_indexes
WHERE idx_owner = 'WIKIQUERY'
AND idx_name = 'WIKI_AC_IDX';This is the result.
IDX_DOCID_COUNT IDX_STATUS MAINTENANCE
--------------- ---------- -----------
25000 INDEXED AUTOThe output shows 25,000 indexed documents and automatic maintenance. With automatic maintenance, Oracle synchronizes the index asynchronously in the background at intervals it determines.
To view attribute values for the objects used by this index—such as the datastore, section group, lexer, wordlist, stoplist, and storage—query CTX_INDEX_VALUES. Filter by both owner and index name:
SELECT ixv_class,
ixv_object,
ixv_attribute,
ixv_value
FROM ctxsys.ctx_index_values
WHERE ixv_index_owner = 'WIKIQUERY'
AND ixv_index_name = 'WIKI_AC_IDX'
ORDER BY ixv_class, ixv_object, ixv_attribute;The result is:
IXV_CLASS IXV_OBJECT IXV_ATTRIBUTE IXV_VALUE
--------------- ------------------------- -------------------- ---------------------------
DATASTORE MULTI_COLUMN_DATASTORE COLUMNS DATA
SECTION_GROUP BASIC_SECTION_GROUP NDATA 1092
SECTION_GROUP BASIC_SECTION_GROUP SECTION_ATTRIBUTE DATA
SECTION_GROUP BASIC_SECTION_GROUP SECTION_ATTRIBUTE 200
SECTION_GROUP BASIC_SECTION_GROUP SECTION_ATTRIBUTE 0
LEXER BASIC_LEXER BASE_LETTER YES
WORDLIST BASIC_WORDLIST PREFIX_INDEX YES
WORDLIST BASIC_WORDLIST PREFIX_MIN_LENGTH 3
WORDLIST BASIC_WORDLIST PREFIX_MAX_LENGTH 6
WORDLIST BASIC_WORDLIST WILDCARD_INDEX YES
WORDLIST BASIC_WORDLIST WILDCARD_INDEX_K 3
STOPLIST BASIC_STOPLIST STOP_WORD
Mr
...
STOPLIST BASIC_STOPLIST STOP_WORD yours
STORAGE BASIC_STORAGE R_TABLE_CLAUSE lob (data) store as (cache)Summary
Oracle AI Database provides database-native autocomplete through DBMS_SEARCH and Oracle Text. Oracle AI Database’s DBMS_SEARCH autocomplete helps users find relevant content with less typing by returning ranked suggestions as they type. It uses an Oracle Text index built on a table column or JSON field. Optional fuzzy matching can help with misspellings, while the NAME domain is designed for name searches. The index is synchronized automatically in the background, so there’s no need to run synchronization manually.
Further Reading
Oracle Text Reference
Full-text search across multiple objects: Ubiquitous Database Search (posting)