AUTOCOMPLETE

DBMS_SEARCH.AUTOCOMPLETE function returns ranked autocomplete suggestions as a JSON array based on the typed characters.

Syntax

DBMS_SEARCH.AUTOCOMPLETE(
   idx_name    VARCHAR2,
   typed_chars VARCHAR2,
   num_results NUMBER   DEFAULT 10,
   fuzzy       BOOLEAN  DEFAULT FALSE,
   domain      VARCHAR2 DEFAULT 'GENERIC'
) RETURN JSON;

idx_name

Specify the name of the autocomplete index to query.

typed_chars

Specify the user’s input text (partial text to complete).

num_results

Specify the maximum number of suggestions to return. The default value is set to 10.

fuzzy

Specify to enables fuzzy matching for near-miss spellings. The default value is set to FALSE.

domain

Specify any one of the following suggestion styles to control autocomplete suggestions in search. The value should match with the domain value provided while creating autocomplete index.

Examples

This example creates an autocomplete index named MOVIES_AC_IDX on the MOVIES_JSON table. It specifically targets the JSON field $.title inside the DOC column. By setting the domain to GENERIC, it configures the index for general text completions (not name-specific). After running this, your application can provide fast, ranked suggestions for movie titles as users begin typing.

SELECT data, score
FROM JSON_TABLE(
  DBMS_SEARCH.AUTOCOMPLETE(
    idx_name    => 'MOVIES_AC_IDX',
    typed_chars => 'harry p',
    num_results => 10
  ),
  '$[*]' COLUMNS (
    data  VARCHAR2(4000) PATH '$.DATA',
    score NUMBER         PATH '$.SCORE'
  )
)
ORDER BY score DESC, data;