DBMS_VECTOR_CHAIN

The DBMS_VECTOR_CHAIN package provides APIs to support advanced operations with Oracle AI Vector Search, such as chunking and embedding data along with text generation and summarization capabilities. It is more suitable for text processing with similarity search and hybrid search, using functionality that can be pipelined together for an end-to-end search.

These functions accept their respective input parameters in JSON format.

Related Topics

Summary of DBMS_VECTOR_CHAIN Subprograms

This table lists the DBMS_VECTOR_CHAIN subprograms and briefly describes them.

Table 1 DBMS_VECTOR_CHAIN Package Subprograms

Subprogram Description

Chainable Utility (UTL) Functions:

These functions are a set of modular and flexible functions within vector utility PL/SQL packages. You can chain these together to automate end-to-end data transformation and similarity search operations.

UTL_TO_TEXT

Extracts plain text data from documents

UTL_TO_CHUNKS

Splits data into smaller pieces or chunks

UTL_TO_EMBEDDING and UTL_TO_EMBEDDINGS

Converts data to one or more vector embeddings

UTL_TO_SUMMARY

Extracts a summary from documents

UTL_TO_GENERATE_TEXT

Generates text for a prompt (input string) or an image

Credential Helper Procedures:

These procedures enable you to securely manage authentication credentials in the database. You require these credentials to enable access to third-party service providers for making REST calls.

CREATE_CREDENTIAL

Creates a credential name

DROP_CREDENTIAL

Drops an existing credential name

Preference Helper Procedures:

These procedures enable you to manage vectorizer preferences, to be used with the CREATE_HYBRID_VECTOR_INDEX and ALTER_INDEX SQL statements when creating or managing hybrid vector indexes.

CREATE_PREFERENCE

Creates a vectorizer preference

DROP_PREFERENCE

Drops an existing vectorizer preference

Chunker Helper Procedures:

These procedures enable you to configure vocabulary and language data (abbreviations), to be used with the VECTOR_CHUNKS SQL function or UTL_TO_CHUNKS PL/SQL function.

CREATE_VOCABULARY

Loads your token vocabulary file into the database

DROP_VOCABULARY

Removes existing vocabulary data

CREATE_LANG_DATA

Loads your language data file into the database

DROP_LANG_DATA

Removes existing abbreviation data

Data Access Function:

This function enables you to enhance search operations.

UTL_TO_RERANK

Reorders search results for more relevant output

Note:

The DBMS_VECTOR_CHAIN package requires you to install the CONTEXT component of Oracle Text, an Oracle AI Database technology that provides indexing, term extraction, text analysis, text summarization, word and theme searching, and other utilities.

Due to underlying dependance on the text processing capabilities of Oracle Text, note that both the UTL_TO_TEXT and UTL_TO_SUMMARY chainable utility functions and all the chunker helper procedures are available only in this package through Oracle Text.

CREATE_CREDENTIAL

Use the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL credential helper procedure to create a credential name for storing user authentication details in Oracle AI Database.

Purpose

To securely manage authentication credentials in the database. You require these credentials to enable access during REST API calls to your chosen third-party service provider, such as Cohere, Google AI, Hugging Face, Oracle Cloud Infrastructure (OCI) Generative AI, OpenAI, or Vertex AI.

A credential name holds authentication parameters, such as user name, password, access token, private key, or fingerprint.

Note that if you are using Oracle AI Database as the service provider, then you do not need to create a credential.

WARNING:

Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.

Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from such access.

Syntax

DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL (
    CREDENTIAL_NAME     IN VARCHAR2,
    PARAMS              IN JSON DEFAULT NULL
);

CREDENTIAL_NAME

Specify a name of the credential that you want to create for holding authentication parameters.

PARAMS

Specify authentication parameters in JSON format, based on your chosen service provider.

Generative AI requires the following authentication parameters:

{
"user_ocid"       : "<user ocid>",
"tenancy_ocid"    : "<tenancy ocid>",
"compartment_ocid": "<compartment ocid>",
"private_key"     : "<private key>",
"fingerprint"     : "<fingerprint>"
}

Cohere, Google AI, Hugging Face, OpenAI, and Vertex AI require the following authentication parameter:

{ "access_token": "<access token>" }

Table 2 Parameter Details

Parameter Description
user_ocid Oracle Cloud Identifier (OCID) of the user, as listed on the User Details page in the OCI console.
tenancy_ocid OCID of your tenancy, as listed on the Tenancy Details page in the OCI console.
compartment_ocid OCID of your compartment, as listed on the Compartments information page in the OCI console.
private_key

OCI private key.

Note: The generated private key may appear as:

-----BEGIN RSA PRIVATE KEY----- <private key string> -----END RSA PRIVATE KEY-----
You pass the <private key string> value (excluding the BEGIN and END lines), either as a single line or as multiple lines.

fingerprint Fingerprint of the OCI profile key, as listed on the User Details page under API Keys in the OCI console.
access_token Access token obtained from your third-party service provider.

Required Privilege

You need the CREATE CREDENTIAL privilege to call this API.

Examples

End-to-end examples:

To run end-to-end example scenarios using this procedure, see Use LLM-Powered APIs to Generate Summary and Text.

CREATE_LANG_DATA

Use the DBMS_VECTOR_CHAIN.CREATE_LANG_DATA chunker helper procedure to load your own language data file into the database.

Purpose

To create custom language data for your chosen language (specified using the language chunking parameter).

A language data file contains language-specific abbreviation tokens. You can supply this data to the chunker to help in accurately determining sentence boundaries of chunks, by using knowledge of the input language’s end-of-sentence (EOS) punctuations, abbreviations, and contextual rules.

Usage Notes

Syntax

DBMS_VECTOR_CHAIN.CREATE_LANG_DATA (
    PARAMS       IN JSON default NULL
);

PARAMS

Specify the input parameters in JSON format:

{
    table_name,
    column_name,
    language,
    preference_name
}

Table 3 Parameter Details

Parameter Description Required Default Value
table_name Name of the table (along with the optional table owner) in which you want to load the language data Yes No value
column_name Column name in the language data table in which you want to load the language data Yes No value
language Any supported language name, as listed in Supported Languages and Data File Locations Yes No value
preference_name User-specified preference name for this language data Yes No value

Example

declare
    params CLOB := '{"table_name"      : "eos_data_1",
                     "column_name"     : "token",
                     "language"        : "indonesian",
                     "preference_name" : "my_lang_1"}';
begin
    DBMS_VECTOR_CHAIN.CREATE_LANG_DATA(
        JSON (params));
end;
/

End-to-end example:

To run an end-to-end example scenario using this procedure, see Create and Use Custom Language Data.

Related Topics

CREATE_PREFERENCE

Use the DBMS_VECTOR_CHAIN.CREATE_PREFERENCE helper procedure to create a vectorizer preference, to be used when creating or updating hybrid vector indexes.

Purpose

To create a vectorizer preference.

This allows you to customize vector search parameters of a hybrid vector indexing pipeline. The goal of a vectorizer preference is to provide you with a straightforward way to configure how to chunk or embed your documents, without requiring a deep understanding of various chunking or embedding strategies.

Usage Notes

A vectorizer preference is a JSON object that collectively holds user-specified values related to the following chunking, embedding, or vector index creation parameters:

All vector index preferences follow the same JSON syntax as defined for their corresponding DBMS_VECTOR and DBMS_VECTOR_CHAIN APIs.

After creating a vectorizer preference, you can use the VECTORIZER parameter to pass this preference name in the paramstring of the PARAMETERS clause for CREATE_HYBRID_VECTOR_INDEX and ALTER_INDEX SQL statements.

Creating a preference is optional. If you do not specify any optional preference, then the index is created with system defaults.

Syntax

DBMS_VECTOR_CHAIN.CREATE_PREFERENCE (
    PREF_NAME     IN VARCHAR2,
    PREF_TYPE     IN VARCHAR2,
    PARAMS        IN JSON default NULL
);

PREF_NAME

Specify the name of the vectorizer preference to create.

PREF_TYPE

Type of preference. The only supported preference type is:

DBMS_VECTOR_CHAIN.VECTORIZER

PARAMS

Specify vector search-specific parameters in JSON format:

Embedding Parameter:

{ "model" : <embedding_model_for_vector_generation> }

For example:

{ "model" : MY_INDB_MODEL }

model specifies the name under which your ONNX embedding model is stored in the database.

If you do not have an in-database embedding model in ONNX format, then perform the steps listed in Oracle AI Database AI Vector Search User’s Guide.

If creating a vectorizer preference with externally hosted vector embedding models:

{
  "embedder_spec": {
    "provider"         : provider_name,
    "url"              : embedding_endpoint_url,
    "proxy"            : proxy_host_and_port,
    "no_proxy"         : no_proxy_list,
    "credential_name"  : db_credential_name,
    "model"            : embedding_model_name
  }
}

Set the following parameters. In addition to the embedding parameters documented below, you may include extra parameters that are specific to your embedding provider/model. These additional parameters are optional.

Table 4 Embedding parameters

Parameter Description
provider

Third-party service provider that you want to access for this operation. A REST call is made to the specified provider to access its embedding model.

For image input, specify vertexai.

For text input, specify one of the following values:

  • cohere

  • googleai

  • huggingface

  • ocigenai

  • openai

  • vertexai

  • mistralai

  • ollama

  • privateai
url URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
proxy Optional. Proxy host:port used for outbound HTTP(S) calls to the embedding endpoint (common in enterprise networks).
no_proxy Optional. Comma-separated list of hosts/domains/IP ranges that should bypass the proxy (for example, local addresses and internal domains).
credential_name

Name of the credential.

A credential name holds authentication credentials to enable access to your provider for making REST API calls.

You need to first set up your credential by calling the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL helper function to create and store a credential, and then refer to the credential name here. See CREATE_CREDENTIAL.

Note:

When using privateai, a credential is not required.
model

Name of the third-party embedding model.

Note:

  • For Generative AI, all the supported third-party models are listed in Supported Third-Party Provider Operations and Endpoints.

  • For accurate results, ensure that the chosen text embedding model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model.

  • To get image embeddings, you can use any image embedding model or multimodal embedding model supported by Vertex AI. Multimodal embedding is a technique that vectorizes data from different modalities such as text and images.

    When using a multimodal embedding model to generate embeddings, ensure that you use the same model to vectorize both types of content (text and images). By doing so, the resulting embeddings are compatible and situated in the same vector space, which allows for effective comparison between the two modalities during similarity searches.

Chunking Parameters:

{
    "by"           :  mode,
    "max"          :  max,
    "overlap"      :  overlap,
    "split"        :  split_condition,
    "vocabulary"   :  vocabulary_name,
    "language"     :  nls_language,
    "normalize"    :  normalize_mode,
    "extended"     :  boolean
}

For example:

JSON(
 '{ "by"           :    "vocabulary",
    "max"          :    "100",
    "overlap"      :    "0",
    "split"        :    "none",
    "vocabulary"   :    "myvocab",
    "language"     :    "american",
    "normalize"    :    "all"
  }')

If you specify split as custom and normalize as options, then you must additionally specify the custom_list and norm_options parameters, respectively:

JSON(
 '{ "by"           :    "vocabulary",
    "max"          :    "100",
    "overlap"      :    "0",
    "split"        :    "custom",
    "custom_list"  :    [ "<p>" , "<s>" ],
    "vocabulary"   :    "myvocab",
    "language"     :    "american",
    "normalize"    :    "options",
    "norm_options" :    [ "whitespace" ]
  }')

The following table describes all the chunking parameters:

Parameter Description and Acceptable Values
by

Specify a mode for splitting your data, that is, to split by counting the number of characters, words, or vocabulary tokens.

Valid values:

  • characters (or chars):

    Splits by counting the number of characters.

  • words:

    Splits by counting the number of words.

    Words are defined as sequences of alphabetic characters, sequences of digits, individual punctuation marks, or symbols. For segmented languages without whitespace word boundaries (such as Chinese, Japanese, or Thai), each native character is considered a word (that is, unigram).

  • vocabulary:

    Splits by counting the number of vocabulary tokens.

    Vocabulary tokens are words or word pieces, recognized by the vocabulary of the tokenizer that your embedding model uses. You can load your vocabulary file using the chunker helper API DBMS_VECTOR_CHAIN.CREATE_VOCABULARY.

    Note: For accurate results, ensure that the chosen model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model.

Default value: words
max

Specify a limit on the maximum size of each chunk. This setting splits the input text at a fixed point where the maximum limit occurs in the larger text. The units of max correspond to the by mode, that is, to split data when it reaches the maximum size limit of a certain number of characters, words, numbers, punctuation marks, or vocabulary tokens.

Valid values:

  • by characters: 50 to 4000 characters

  • by words: 10 to 1000 words

  • by vocabulary: 10 to 1000 tokens

Default value: 100
split [by]

Specify where to split the input text when it reaches the maximum size limit. This helps to keep related data together by defining appropriate boundaries for chunks.

Valid values:

  • none:

    Splits at the max limit of characters, words, or vocabulary tokens.

  • newline, blankline, and space:

    These are single-split character conditions that split at the last split character before the max value.

    Use newline to split at the end of a line of text. Use blankline to split at the end of a blank line (sequence of characters, such as two newlines). Use space to split at the end of a blank space.

  • recursively:

    This is a multiple-split character condition that breaks the input text using an ordered list of characters (or sequences).

    recursively is predefined as BLANKLINE, newline, space, none in this order:

    1. If the input text is more than the max value, then split by the first split character.

    2. If that fails, then split by the second split character.

    3. And so on.

    4. If no split characters exist, then split by max wherever it appears in the text.

  • sentence:

    This is an end-of-sentence split condition that breaks the input text at a sentence boundary.

    This condition automatically determines sentence boundaries by using knowledge of the input language's sentence punctuation and contextual rules. This language-specific condition relies mostly on end-of-sentence (EOS) punctuations and common abbreviations.

    Contextual rules are based on word information, so this condition is only valid when splitting the text by words or vocabulary (not by characters).

    Note: This condition obeys the by word and max settings, and thus may not determine accurate sentence boundaries in some cases. For example, when a sentence is larger than the max value, it splits the sentence at max. Similarly, it includes multiple sentences in the text only when they fit within the max limit.

  • custom:

    Splits based on a custom split characters list. You can provide custom sequences up to a limit of 16 split character strings, with a maximum length of 10 each.

    Specify an array of valid text literals using the custom_list parameter. 

    { "split" : "custom", "custom_list" : [ "split_chars1", ... ] }

    For example:

    { "split" : "custom", "custom_list" : [ "<p>" , "<s>" ] }

    Note: You can omit sequences only for tab (\t), newline (\n), and linefeed (\r).

Default value: recursively
overlap

Specify the amount (as a positive integer literal or zero) of the preceding text that the chunk should contain, if any. This helps in logically splitting up related text (such as a sentence) by including some amount of the preceding chunk text.

The amount of overlap depends on how the maximum size of the chunk is measured (in characters, words, or vocabulary tokens). The overlap begins at the specified split condition (for example, at newline).

Valid value: 5% to 20% of max

Default value: 0
language

Specify the language of your input data.

This clause is important, especially when your text contains certain characters (for example, punctuations or abbreviations) that may be interpreted differently in another language.

Valid values:

Note: You must use escape characters with any language abbreviation that is also a SQL reserved word (for example, language abbreviations such as IN, AS, OR, IS).

For example:

SELECT dbms_vector_chain.utl_to_chunks('this is an example',  JSON('{ "language" : \"in\" }'))  from dual;
SELECT dbms_vector_chain.utl_to_chunks('this is an example',  JSON_OBJECT('language' value '"in"' RETURNING JSON))  from dual;

Default value: NLS_LANGUAGE from session

normalize

Automatically pre-processes or post-processes issues (such as multiple consecutive spaces and smart quotes) that may arise when documents are converted into text. Oracle recommends you to use a normalization mode to extract high-quality chunks.

Valid values:

  • none:

    Applies no normalization.

  • all:

    Normalizes common multi-byte (unicode) punctuation to standard single-byte.

  • options:

    Specify an array of normalization options using the norm_options parameter. 

    { "normalize" : "options", "norm_options" : [ "normalize_option1", ... ] }

    • punctuation:

      Converts quotes, dashes, and other punctuation characters supported in the character set of the text to their common ASCII form. For example:

      • U+2013 (En Dash) maps to U+002D (Hyphen-Minus)

      • U+2018 (Left Single Quotation Mark) maps to U+0027 (Apostrophe)

      • U+2019 (Right Single Quotation Mark) maps to U+0027 (Apostrophe)

      • U+201B (Single High-Reversed-9 Quotation Mark) maps to U+0027 (Apostrophe)

    • whitespace:

      Minimizes whitespace by eliminating unnecessary characters.

      For example, retain blanklines, but remove any extra newlines and interspersed spaces or tabs: " \n \n " => "\n\n"

    • widechar:

      Normalizes wide, multi-byte digits and (a-z) letters to single-byte.

      These are multi-byte equivalents for 0-9 and a-z A-Z, which can show up in Chinese, Japanese, or Korean text.

    For example:

    { "normalize" : "options", "norm_options" : [ "whitespace" ] }

Default value: none
extended

Increases the output limit of a VARCHAR2 string to 32767 bytes, without requiring you to set the max_string_size parameter to extended.

Default value: 4000 or 32767 (when max_string_size=extended)

Vector Index Parameters:

{
  "distance"        :  <vector_distance>,
  "accuracy"        :  <vector_accuracy>,
  "vector_idxtype"  :  <vector_idxtype>
}

For example:

{
  "distance"        :  COSINE,
  "accuracy"        :  95,
  "vector_idxtype"  :  HNSW
}
Parameter Description
distance

Distance metric or mathematical function used to compute the distance between vectors:

  • COSINE
  • MANHATTAN
  • DOT
  • EUCLIDEAN
  • L2_SQUARED
  • EUCLIDEAN_SQUARED

Note: Currently, the HAMMING and JACCARD vector distance metrics are not supported with hybrid vector indexes.

For detailed information on each of these metrics, see Vector Distance Functions and Operators.

Default value: COSINE
accuracy

Target accuracy at which the approximate search should be performed when running an approximate search query using vector indexes.

As explained in Understand Approximate Similarity Search Using Vector Indexes, you can specify non-default target accuracy values either by specifying a percentage value or by specifying internal parameters values, depending on the index type you are using.

  • For a Hierarchical Navigable Small World (HNSW) approximate search:

    In the case of an HNSW approximate search, you can specify a target accuracy percentage value to influence the number of candidates considered to probe the search. This is automatically calculated by the algorithm. A value of 100 will tend to impose a similar result as an exact search, although the system may still use the index and will not perform an exact search. The optimizer may choose to still use an index as it may be faster to do so given the predicates in the query. Instead of specifying a target accuracy percentage value, you can specify the EFSEARCH parameter to impose a certain maximum number of candidates to be considered while probing the index. The higher that number, the higher the accuracy.

    For detailed information, see Understand Hierarchical Navigable Small World Indexes.

  • For an Inverted File Flat (IVF) approximate search:

    In the case of an IVF approximate search, you can specify a target accuracy percentage value to influence the number of partitions used to probe the search. This is automatically calculated by the algorithm. A value of 100 will tend to impose an exact search, although the system may still use the index and will not perform an exact search. The optimizer may choose to still use an index as it may be faster to do so given the predicates in the query. Instead of specifying a target accuracy percentage value, you can specify the NEIGHBOR PARTITION PROBES parameter to impose a certain maximum number of partitions to be probed by the search. The higher that number, the higher the accuracy.

    For detailed information, see Understand Inverted File Flat Vector Indexes.

Valid range for both HNSW and IVF vector indexes is:

> 0 and <= 100< code>

Default value: None

vector_idxtype

Type of vector index to create:

  • HNSW for an HNSW vector index

  • IVF for an IVF vector index

For detailed information on each of these index types, see Manage the Different Categories of Vector Indexes.

Default value: IVF

Paths Parameter:

This field allows specification of an array of path objects. There can be many path objects and each path object must specify a type and a path_list.

Note: If the user does not specify the paths field, the whole document would be considered.

"paths":[
        {"type"      : "<path_type>",
         "path_list" : ["<path_list_array>"]
        }
        ]

Let us consider a sample JSON document:

{
     "person":
      {
        "bio": "James is a data scientist who specializes in natural language .. ",
        "profile":
         {
             "text" : "James is a data scientist with expertise in Python programming...",
             "embedding" : [
1.60541728E-001,5.76677322E-002,4.0473938E-003,1.2037459E-001,-5.98970801E-004, ..]
         },
        "avatar": "https://example.com/images/James.jpg"
      },
     "product":
     {
       "description": "A social media analytics tool.", "It helps brands track...",
       "image": "https://example.com/images/data_tool.jpg",
       "embedding" : [1.60541728E-001,5.76677322E-002,4.0473938E-003,1.2037459E-001,-5.98970801E-004, ..]
     }
}

And a path_list corresponding to the above JSON is provided here:

"paths": [
         {"type"       : "VECTOR",
          "path_list" : ["$.person.profile.embedding", "$.product.embedding"]
         },
         {"type"       : "STRING",
          "path_list" : ["$.person.bio", "$.product.description"]
         }
         ]

The following table describes the details of paths parameter:

Parameter Accepted Values
type

The possible values for this field are:

  • STRING - for fields that need to be converted to vectors.

  • VECTOR - for fields that are already vectors.

path_list

Accepts an array of paths with at least one path in valid JSON format - ($.a.b.c.d).

Note: For the VECTOR option, Oracle currently accepts one vector array per path.

Example

begin
  DBMS_VECTOR_CHAIN.CREATE_PREFERENCE(
    'my_vec_spec',
     DBMS_VECTOR_CHAIN.VECTORIZER,
     json('{ "vector_idxtype" :  "hnsw",
             "model"          :  "my_doc_model",
             "by"             :  "words",
             "max"            :  100,
             "overlap"        :  10,
             "split"          :  "recursively,
             "language"       :  "english",
             "paths":          : [
                                 {
                                  "type"      : "VECTOR",
                                  "path_list" : ["$.person.profile.embedding"]
                                  }
                                 ]
              }'));
end;
/

CREATE HYBRID VECTOR INDEX my_hybrid_idx on
    doc_table(text_column)
    parameters('VECTORIZER my_vec_spec');

Related Topics

CREATE_VOCABULARY

Use the DBMS_VECTOR_CHAIN.CREATE_VOCABULARY chunker helper procedure to load your own token vocabulary file into the database.

Purpose

To create custom token vocabulary that is recognized by the tokenizer used by your vector embedding model.

A vocabulary contains a set of tokens (words and word pieces) that are collected during a model’s statistical training process. You can supply this data to the chunker to help in accurately selecting the text size that approximates the maximum input limit imposed by the tokenizer of your embedding model.

Usage Notes

Syntax

DBMS_VECTOR_CHAIN.CREATE_VOCABULARY(
    PARAMS      IN JSON default NULL
);

PARAMS

Specify the input parameters in JSON format:

{
    table_name,
    column_name,
    vocabulary_name,
    format,
    cased
}

Table 5 Parameter Details

Parameter Description Required Default Value
table_name Name of the table (along with the optional table owner) in which you want to load the vocabulary file Yes No value
column_name Column name in the vocabulary table in which you want to load the vocabulary file Yes No value
vocabulary_name User-specified name of the vocabulary, along with the optional owner name (if other than the current owner) Yes No value
format

  • xlm for SentencePiece tokenization

  • bert for WordPiece tokenization

  • gpt2 for BPE tokenization

Yes <p>No value</p>
cased Character-casing of the vocabulary, that is, vocabulary to be treated as cased or uncased No false

Example

DECLARE
  params clob := '{"table_name"       : "doc_vocabtab",
                   "column_name"      : "token",
                   "vocabulary_name"  : "doc_vocab",
                   "format"           : "bert",
                   "cased"            : false}';

BEGIN
  dbms_vector_chain.create_vocabulary(json(params));
END;
/

End-to-end example:

To run an end-to-end example scenario using this procedure, see Create and Use Custom Vocabulary.

Related Topics

DROP_CREDENTIAL

Use the DBMS_VECTOR_CHAIN.DROP_CREDENTIAL credential helper procedure to drop an existing credential name from the data dictionary.

Syntax

DBMS_VECTOR_CHAIN.DROP_CREDENTIAL (
    CREDENTIAL_NAME      IN VARCHAR2
);

CREDENTIAL_NAME

Specify the credential name that you want to drop.

Examples

DROP_LANG_DATA

Use the DBMS_VECTOR_CHAIN.DROP_LANG_DATA chunker helper procedure to remove abbreviation data from the data dictionary.

Syntax

DBMS_VECTOR_CHAIN.DROP_LANG_DATA(
    PREF_NAME     IN VARCHAR2
);

LANG

Specify the name of the language data that you want to drop for a given language.

Example

DBMS_VECTOR_CHAIN.DROP_LANG_DATA('indonesian');

DROP_PREFERENCE

Use the DBMS_VECTOR_CHAIN.DROP_PREFERENCE preference helper procedure to remove an existing Vectorizer preference.

Syntax

DBMS_VECTOR_CHAIN.DROP_PREFERENCE (PREF_NAME);

PREF_NAME

Name of the Vectorizer preference to drop.

Example

DBMS_VECTOR_CHAIN.DROP_PREFERENCE ('scott_vectorizer');

DROP_VOCABULARY

Use the DBMS_VECTOR_CHAIN.DROP_VOCABULARY chunker helper procedure to remove vocabulary data from the data dictionary.

Syntax

DBMS_VECTOR_CHAIN.DROP_VOCABULARY(
    VOCABULARY_NAME    IN VARCHAR2
);

VOCAB_NAME

Specify the name of the vocabulary that you want to drop, in the form:

vocabulary_name

or

owner.vocabulary_name

Example

DBMS_VECTOR_CHAIN.DROP_VOCABULARY('MY_VOCAB_1');

UTL_TO_RERANK

Use the DBMS_VECTOR_CHAIN.UTL_TO_RERANK function to reassess and reorder an initial set of results to retrieve more relevant search output.

Purpose

To improve the relevance and quality of search results in both similarity search and Retrieval Augmented Generation (RAG) scenarios.

Reranking improves the quality of information ingested into an LLM by ensuring that the most relevant documents or chunks are prioritized. This helps to reduce hallucinations and improves the accuracy of generated outputs.

For this operation, Oracle AI Vector Search supports reranking models provided by Cohere, Vertex AI, ocigenai, and ONNX (Oracle AI Database as the service provider). However, ocigenai is available only in Dedicated mode.

WARNING:

Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.

Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from such access.

Syntax

DBMS_VECTOR_CHAIN.UTL_TO_RERANK(
                QUERY      IN CLOB,
                DOCUMENTS  IN JSON,
                PARAMS     IN JSON default NULL
) return JSON;

This function accepts the input containing a query as CLOB and a list of documents in JSON format. It then processes this information to generate a JSON object containing a reranked list of documents, sorted by score.

For example, a reranked output includes:

{
    "index"   : "1",
    "score"   : "0.99",
    "content" : "Jupiter boasts an impressive system of 95 known moons."
}

Where,

QUERY

Specify the search query (typically from an initial search) as CLOB.

DOCUMENTS

Specify a JSON array of strings (list of potentially relevant documents to rerank) in the following format:

{
  "documents": [
  "string1",
  "string2",
    ...
  ]
}

PARAMS

Specify the following list of parameters in JSON format. All these parameters are mandatory.

{
  "provider"         : "<service provider>",
  "credential_name"  : "<credential name>",
  "url"              : "<REST endpoint URL for reranking>",
  "model"            : "<reranking model name>",
  ...
}

Table 6 UTL_TO_RERANK Parameter Details

Parameter Description
provider

Supported REST provider to access for reranking:

  • cohere

  • vertexai
credential_name

Name of the credential in the form:

schema.credential_name

A credential name holds authentication credentials to enable access to your provider for making REST API calls.

You need to first set up your credential by calling the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL helper function to create and store a credential, and then refer to the credential name here.

See CREATE_CREDENTIAL.

url URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
model

Name of the reranking model in the form:

schema.model_name

If the model name is not schema-qualified, then the schema of the procedure invoker is used.

Additional REST provider parameters:

Optionally, specify additional provider-specific parameters for reranking.

Important:

Cohere example:

{
  "provider"        : "cohere",
  "credential_name" : "COHERE_CRED",
  "url"             : "https://api.cohere.example.com/rerank",
  "model"           : "rerank-english-v3.0",
  "return_documents": false,
  "top_n"           : 3
}

Vertex AI example:

{
  "provider"         : "vertexai",
  "credential_name"  : "VERTEXAI_CRED",
  "url"              : "https://googleapis.example.com/default_ranking_config:rank",
  "model"            : "semantic-ranker-512@latest",
  "ignoreRecordDetailsInResponse" : true,
  "topN"             : 3
  }

ONNX (Oracle AI Database as the service provider) example:

{
   "provider": "database",
   "model": "<model_name>",
   "return_docs": true,
   "top_n": 3
}

OCI Generative AI (Dedicated mode) example:

{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/rerankText",
  "model": "ocid1.generativeaiendpoint.oc1.us-chicago-1...",
  "topN": 3,
  "isEcho": true
}

Note: For Dedicated mode, specify the model OCID in the model parameter instead of the model name.

Table 7 Additional REST Provider Parameter Details

Parameter Description
return_documents

Whether to return search results with original documents or input text (content):

  • false (default, also recommended) to not return any input text (return only index and score)

  • true to return input text along with index and score

Note: With Cohere as the provider, Oracle recommends that you keep this option disabled for better performance. You may choose to enable it for debugging purposes when you need to view the original text.

ignoreRecordDetailsInResponse

Whether to return search results with original record details or input text (content):

  • false (default) to return input text along with index and score

  • true (recommended) to not return any input text (return only index and score)

Note: With Vertex AI as the provider, Oracle recommends that you keep this option enabled for better performance. You may choose to disable it for debugging purposes when you need to view the original text.

top_n or topN The number of most relevant documents to return.

Examples

End-to-end example:

To run an end-to-end example scenario using this function, see Use Reranking for Better RAG Results.

UTL_TO_CHUNKS

Use the DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS chainable utility function to split a large plain text document into smaller chunks of text.

Purpose

To perform a text-to-chunks transformation. This chainable utility function internally calls the VECTOR_CHUNKS SQL function for the operation.

To embed a large document, you may first need to split it into multiple appropriate-sized segments or chunks through a splitting process known as chunking (as explained in Understand the Stages of Data Transformations). A chunk can be words (to capture specific words or word pieces), sentences (to capture a specific meaning), or paragraphs (to capture broader themes). A single document may be split into multiple chunks, each transformed into a vector.

Syntax

DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS (
    DATA         IN  CLOB | VARCHAR2,
    PARAMS       IN  JSON  default  NULL
) return VECTOR_ARRAY_T;

DATA

This function accepts the input data type as CLOB or VARCHAR2.

It returns an array of CLOBs, where each CLOB contains a chunk along with its metadata in JSON format, as follows:

{
    "chunk_id"     : NUMBER,
    "chunk_offset" : NUMBER,
    "chunk_length" : NUMBER,
    "chunk_data"   : "VARCHAR2(4000)"
}

For example:

{"chunk_id":1,"chunk_offset":1,"chunk_length":6,"chunk_data":"sample"}

Where,

PARAMS

Specify input parameters in JSON format.

{
    "limit"           :     limit,
    "by"              :     mode,
    "max"             :     max,
    "overlap"         :     overlap,
    "split"           :     split_condition,
    "custom_list"     :     [ split_chars1, ... ],
    "vocabulary"      :     vocabulary_name,
    "language"        :     nls_language,
    "normalize"       :     normalize_mode,
    "norm_options"    :     [ normalize_option1, ... ],
    "join"            :     join_mode_or_characters,
    "extended"        :     boolean,
    "aggregate"       :     aggregate_size,
    "aggregate_mode"  :     aggregation_mode,
    "aggregate_thresh":     aggregate_threshold
}

For example:

JSON('
  { "by"              :    "vocabulary",
    "vocabulary"      :    "myvocab",
    "max"             :    "100",
    "overlap"         :    "0",
    "split"           :    "custom",
    "custom_list"     :    [ "<p>" , "<s>" ],
    "language"        :    "american",
    "normalize"       :    "options",
    "norm_options"    :    [ "whitespace" ]
    "join"            :    "default",
    "aggregate"       :    "400",
    "aggregate_mode"  :    "words",
    "aggregate_thresh":    "20"
  }')

Here is a complete description of these parameters:

Parameter Description and Acceptable Values
by

Specify a mode for splitting your data, that is, to split by counting the number of characters, words, or vocabulary tokens.

Valid values:

  • characters (or chars):

    Splits by counting the number of characters.

  • words:

    Splits by counting the number of words.

    Words are defined as sequences of alphabetic characters, sequences of digits, individual punctuation marks, or symbols. For segmented languages without whitespace word boundaries (such as Chinese, Japanese, or Thai), each native character is considered a word (that is, unigram).

  • vocabulary:

    Splits by counting the number of vocabulary tokens.

    Vocabulary tokens are words or word pieces, recognized by the vocabulary of the tokenizer that your embedding model uses. You can load your vocabulary file using the chunker helper API DBMS_VECTOR_CHAIN.CREATE_VOCABULARY.

    Note: For accurate results, ensure that the chosen model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model.

Default value: words
max

Specify a limit on the maximum size of each chunk. This setting splits the input text at a fixed point where the maximum limit occurs in the larger text. The units of max correspond to the by mode, that is, to split data when it reaches the maximum size limit of a certain number of characters, words, numbers, punctuation marks, or vocabulary tokens.

Valid values:

  • by characters: 50 to 4000 characters

  • by words: 10 to 1000 words

  • by vocabulary: 10 to 1000 tokens

Default value: 100
split [by]

Specify where to split the input text when it reaches the maximum size limit. This helps to keep related data together by defining appropriate boundaries for chunks.

Valid values:

  • none:

    Splits at the max limit of characters, words, or vocabulary tokens.

  • newline, blankline, and space:

    These are single-split character conditions that split at the last split character before the max value.

    Use newline to split at the end of a line of text. Use blankline to split at the end of a blank line (sequence of characters, such as two newlines). Use space to split at the end of a blank space.

  • recursively:

    This is a multiple-split character condition that breaks the input text using an ordered list of characters (or sequences).

    recursively is predefined as BLANKLINE, newline, space, none in this order:

    1. If the input text is more than the max value, then split by the first split character.

    2. If that fails, then split by the second split character.

    3. And so on.

    4. If no split characters exist, then split by max wherever it appears in the text.

  • sentence:

    This is an end-of-sentence split condition that breaks the input text at a sentence boundary.

    This condition automatically determines sentence boundaries by using knowledge of the input language's sentence punctuation and contextual rules. This language-specific condition relies mostly on end-of-sentence (EOS) punctuations and common abbreviations.

    Contextual rules are based on word information, so this condition is only valid when splitting the text by words or vocabulary (not by characters).

    Note: This condition obeys the by word and max settings, and thus may not determine accurate sentence boundaries in some cases. For example, when a sentence is larger than the max value, it splits the sentence at max. Similarly, it includes multiple sentences in the text only when they fit within the max limit.

  • custom:

    Splits based on a custom split characters list. You can provide custom sequences up to a limit of 16 split character strings, with a maximum length of 10 each.

    Specify an array of valid text literals using the custom_list parameter. 

    { "split" : "custom", "custom_list" : [ "split_chars1", ... ] }

    For example:

    { "split" : "custom", "custom_list" : [ "<p>" , "<s>" ] }

    Note: You can omit sequences only for tab (\t), newline (\n), and linefeed (\r).

Default value: recursively
overlap

Specify the amount (as a positive integer literal or zero) of the preceding text that the chunk should contain, if any. This helps in logically splitting up related text (such as a sentence) by including some amount of the preceding chunk text.

The amount of overlap depends on how the maximum size of the chunk is measured (in characters, words, or vocabulary tokens). The overlap begins at the specified split condition (for example, at newline).

Valid value: 5% to 20% of max

Default value: 0
language

Specify the language of your input data.

This clause is important, especially when your text contains certain characters (for example, punctuations or abbreviations) that may be interpreted differently in another language.

Valid values:

Note: You must use escape characters with any language abbreviation that is also a SQL reserved word (for example, language abbreviations such as IN, AS, OR, IS).

For example:

SELECT dbms_vector_chain.utl_to_chunks('this is an example',  JSON('{ "language" : \"in\" }'))  from dual;
SELECT dbms_vector_chain.utl_to_chunks('this is an example',  JSON_OBJECT('language' value '"in"' RETURNING JSON))  from dual;

Default value: NLS_LANGUAGE from session

normalize

Automatically pre-processes or post-processes issues (such as multiple consecutive spaces and smart quotes) that may arise when documents are converted into text. Oracle recommends you to use a normalization mode to extract high-quality chunks.

Valid values:

  • none:

    Applies no normalization.

  • all:

    Normalizes common multi-byte (unicode) punctuation to standard single-byte.

  • options:

    Specify an array of normalization options using the norm_options parameter. 

    { "normalize" : "options", "norm_options" : [ "normalize_option1", ... ] }

    • punctuation:

      Converts quotes, dashes, and other punctuation characters supported in the character set of the text to their common ASCII form. For example:

      • U+2013 (En Dash) maps to U+002D (Hyphen-Minus)

      • U+2018 (Left Single Quotation Mark) maps to U+0027 (Apostrophe)

      • U+2019 (Right Single Quotation Mark) maps to U+0027 (Apostrophe)

      • U+201B (Single High-Reversed-9 Quotation Mark) maps to U+0027 (Apostrophe)

    • whitespace:

      Minimizes whitespace by eliminating unnecessary characters.

      For example, retain blanklines, but remove any extra newlines and interspersed spaces or tabs: " \n \n " => "\n\n"

    • widechar:

      Normalizes wide, multi-byte digits and (a-z) letters to single-byte.

      These are multi-byte equivalents for 0-9 and a-z A-Z, which can show up in Chinese, Japanese, or Korean text.

    For example:

    { "normalize" : "options", "norm_options" : [ "whitespace" ] }

Default value: none
extended

Increases the output limit of a VARCHAR2 string to 32767 bytes, without requiring you to set the max_string_size parameter to extended.

Default value: 4000 or 32767 (when max_string_size=extended)

Example

SELECT D.id doc,
    JSON_VALUE(C.column_value, '$.chunk_id' RETURNING NUMBER) AS id,
    JSON_VALUE(C.column_value, '$.chunk_offset' RETURNING NUMBER) AS pos,
    JSON_VALUE(C.column_value, '$.chunk_length' RETURNING NUMBER) AS siz,
    JSON_VALUE(C.column_value, '$.chunk_data') AS txt
FROM docs D,
   dbms_vector_chain.utl_to_chunks(D.text,
   JSON('{ "by"       : "words",
           "max"      : "100",
           "overlap"  : "0",
           "split"    : "recursively",
           "language" : "american",
           "normalize": "all" }')) C;

End-to-end examples:

To run end-to-end example scenarios using this function, see Perform Chunking With Embedding and Configure Chunking Parameters.

Related Topics

UTL_TO_EMBEDDING and UTL_TO_EMBEDDINGS

Use the DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING and DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS chainable utility functions to generate one or more vector embeddings from textual documents and images.

Purpose

To automatically generate one or more vector embeddings from textual documents and images.

WARNING:

Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.

Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from such access.

Syntax

DATA

Note: Although data is a CLOB or a VECTOR_ARRAY_T of CLOB, the maximum input is 4000 characters. If you have input that is greater, you can use UTL_TO_CHUNKS to split the data into smaller chunks before passing in.

A generated embedding output includes:

{
    "embed_id"    : NUMBER,
    "embed_data"  : "VARCHAR2(4000)",
    "embed_vector": "CLOB"
}

Where,

MODALITY

For BLOB inputs, specify the type of content to vectorize. The only supported value is image.

Note: For image BLOB input, standalone baseline 8-bit lossy JPEG images are accepted. Unsupported or malformed image input does not produce a successful image decode. At SQL scoring time, this can result in a NULL embedding result. Perform any required image preprocessing or format conversion externally before embedding creation.

For more information about image input restrictions, see ONNX Pipeline Models: Image Embedding.

PARAMS

Specify input parameters in JSON format, depending on the service provider that you want to use.

If using Oracle AI Database as the provider:

{
  "provider" : "database",
  "model"    : "<in-database ONNX embedding model name>"
}

Table 8 Database Provider Parameter Details

Parameter Description
provider <p>Specify database (default setting) to use Oracle AI Database as the provider. With this setting, you must load an ONNX format embedding model into the database. </p>
model

User-specified name under which the imported ONNX embedding model is stored in Oracle AI Database.

If you do not have an embedding model in ONNX format, then perform the steps listed in Convert Pretrained Models to ONNX Format.

If using a third-party provider:

Set the following parameters along with additional embedding parameters specific to your provider:

These are the details on each of the input parameters for DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING and DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS when you specify a third-party provider.

When using with Private AI, specify either a credential_name for remote service authentication or use local in the host key for local, unauthenticated access.

Table 9 Third-Party Provider Parameter

Parameter Description
provider

Third-party service provider that you want to access for this operation. A REST call is made to the specified provider to access its embedding model.

For image input, specify vertexai.

For text input, specify one of the following values:

  • cohere

  • googleai

  • huggingface

  • ocigenai

  • openai

  • vertexai

  • mistralai

  • ollama

  • privateai
credential_name

Name of the credential in the form:

schema.credential_name

A credential name holds authentication credentials to enable access to your provider for making REST API calls.

You need to first set up your credential by calling the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL helper function to create and store a credential, and then refer to the credential name here. See CREATE_CREDENTIAL.

host

When using a local service provider, host can be used to disable credential by setting the value to local. When the host parameter is specified, credential_name should be omitted.

The host parameter is applicable only if provider is one of the following:

  • openai
  • ollama
  • privateai
url URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
model

Name of the third-party embedding model in the form:

schema.model_name

If you do not specify a schema, then the schema of the procedure invoker is used.

Note:

  • For Generative AI, all the supported third-party models are listed in Supported Third-Party Provider Operations and Endpoints.

  • For accurate results, ensure that the chosen text embedding model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model.

  • To get image embeddings, you can use any image embedding model or multimodal embedding model supported by Vertex AI. Multimodal embedding is a technique that vectorizes data from different modalities such as text and images.

    When using a multimodal embedding model to generate embeddings, ensure that you use the same model to vectorize both types of content (text and images). By doing so, the resulting embeddings are compatible and situated in the same vector space, which allows for effective comparison between the two modalities during similarity searches.
transfer_timeout

Maximum time to wait for the request to complete.

The default value is 60 seconds. You can increase this value for busy web servers.

batch_size

Maximum number of vectors to request at a time.

For example, for a batch size of 50, if 100 chunks are passed, then this API sends two requests with an array of 50 strings each. If 30 chunks are passed (which is lesser than the defined batch size), then the API sends those in a single request.

For REST calls, it is more efficient to send a batch of inputs at a time rather than requesting a single input per call. Increasing the batch size can provide better performance, whereas reducing the batch size may reduce memory and data usage, especially if your provider has a rate limit.

The default or maximum allowed value depends on the third-party provider settings.
max_count

Maximum number of times the API can be called for a given third-party provider.

When set to an integer n, max_count stops the execution of the API for the given provider beyond n times. This prevents accidental calling of a third-party over some limit, for example to avoid surpassing the service amount that you have purchased.

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

Table 10 Additional REST Provider Parameter Details

Parameter Description
input_type Type of input to vectorize.

Let us look at some example configurations for all third-party providers:

Important:

Cohere example:

{
  "provider"       : "cohere",
  "credential_name": "COHERE_CRED",
  "url"            : "https://api.cohere.example.com/embed",
  "model"          : "embed-english-light-v2.0",
  "input_type"     : "search_query"
}

OCI Generative AI (On-Demand mode) example:

{
  "provider"       : "ocigenai",
  "credential_name": "OCI_CRED",
  "url"            : "https://generativeai.oci.example.com/embedText",
  "model"          : "cohere.embed-english-v3.0",
  "batch_size"     : 10
}

OCI Generative AI (Dedicated mode) example:

{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
  "model": "ocid1.generativeaiendpoint.oc1.us-chicago-1..."
}

Note: For Dedicated mode, specify the model OCID in the model parameter instead of the model name.

Google AI example:

{
  "provider"       : "googleai",
  "credential_name": "GOOGLEAI_CRED",
  "url"            : "https://googleapis.example.com/models/",
  "model"          : "embedding-001"
}

Hugging Face example:

{
  "provider"       : "huggingface",
  "credential_name": "HF_CRED",
  "url"            : "https://api.huggingface.example.com/",
  "model"          : "sentence-transformers/all-MiniLM-L6-v2"
}

Ollama example:

{
  "provider"       : "ollama",
  "host"           : "local",
  "url"            : "http://localhost:11434/api/embeddings",
  "model"          : "phi3:mini"
}

OpenAI example:

{
  "provider"       : "openai",
  "credential_name": "OPENAI_CRED",
  "url"            : "https://api.openai.example.com/v1/embeddings",
  "model"          : "text-embedding-3-small"
}

OpenAI-compatible provider example:

Note: This is just one example of how to set up the configuration for a number of OpenAI-compatible third-party providers, such as Llamafile and vLLM. The provider value must specify openai while the url value depends on your chosen third-party provider’s REST endpoint.

Set host to local to disable credential. Set model to the desired model if the third-party provider requires a model name (for example, Ollama, vLLM, and so on). Otherwise, you can set model to any string such as any (for example, if using Llamafile).

{
  "provider": "openai",
  "url": "http://localhost:8080/v1/embeddings",
  "host": "local",
  "model": "any"
}

Vertex AI example:

{
  "provider"       : "vertexai",
  "credential_name": "VERTEXAI_CRED",
  "url"            : "https://googleapis.example.com/models/",
  "model"          : "textembedding-gecko:predict"
}

Mistral AI example:

{
   "provider": "mistralai",
   "credential_name": "MISTRALAI_CRED",
   "url": "https://api.mistral.ai/v1/embeddings",
   "model": "mistral-embed"
}

Private AI example:

{
  "provider": "privateai",
  "url": "http://host:port/v1/embeddings",
  "host": "local",
  "model": "all-minilm-l12-v2"
}

Examples

You can use UTL_TO_EMBEDDING in a SELECT clause and UTL_TO_EMBEDDINGS in a FROM clause, as follows:

UTL_TO_EMBEDDING:

UTL_TO_EMBEDDINGS:

UTL_TO_GENERATE_TEXT

Use the DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT chainable utility function to generate a text response for a given prompt or an image, by accessing third-party text generation models.

Purpose

To communicate with Large Language Models (LLMs) through natural language conversations. You can generate a textual answer, description, or summary for prompts and images, given as input to LLM-powered chat interfaces.

WARNING:

Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.

Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from such access.

Syntax

This function accepts the input as CLOB containing text data (for textual prompts) or as BLOB containing media data (for media files such as images). It then processes this information to generate a new CLOB containing the generated text.

DATA and TEXT_DATA

Specify the textual prompt as CLOB for the DATA or TEXT_DATA clause.

Note: Hugging Face uses an image captioning model that does not require a prompt, when giving an image as input. If you input a prompt along with an image, then the prompt will be ignored.

MEDIA_DATA

Specify the BLOB file, such as an image or a visual PDF file.

MEDIA_TYPE

Specify the image format for the given image or visual PDF file (BLOB file) in one of the supported image data MIME types. For example:

Note: For a complete list of the supported image formats, refer to your third-party provider’s documentation.

PARAMS

Specify the following input parameters in JSON format, depending on the service provider that you want to access for text generation:

{
  "provider"        : "<AI service provider>",
  "credential_name" : "<credential name>",
  "url"             : "<REST endpoint URL for text generation service>",
  "model"           : "<text generation model name>",
  "transfer_timeout": <maximum wait time for the request to complete>,
  "max_count": "<maximum calls to the AI service provider>",
  "<additional REST provider parameter>": "<REST provider parameter value>"
}

Table 11 UTL_TO_GENERATE_TEXT Parameter Details

Parameter Description
provider

Supported REST provider that you want to access to generate text.

Specify one of the following values:

For CLOB input:

  • cohere

  • googleai

  • huggingface

  • ocigenai

  • openai

  • vertexai

  • mistralai

  • anthropic

  • ollama

For BLOB input:

  • googleai

  • huggingface

  • openai

  • vertexai

  • mistralai

  • anthropic

  • ollama
credential_name

Name of the credential in the form:

schema.credential_name

A credential name holds authentication credentials to enable access to your provider for making REST API calls.

You need to first set up your credential by calling the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL helper function to create and store a credential, and then refer to the credential name here. See CREATE_CREDENTIAL.

host

When using a local service provider, host can be used to disable credential by setting the value to local. When the host parameter is specified, credential_name should be omitted.

The host parameter is applicable only if provider is one of the following:

  • openai
  • ollama
url URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
model

Name of the third-party text generation model in the form:

schema.model_name

If the model name is not schema-qualified, then the schema of the procedure invoker is used.

Note: For Generative AI, all the supported third-party models are listed in Supported Third-Party Provider Operations and Endpoints.

transfer_timeout

Maximum time to wait for the request to complete.

The default value is 60 seconds. You can increase this value for busy web servers.

max_count

Maximum number of times the API can be called for a given third-party provider.

When set to an integer n, max_count stops the execution of the API for the given provider beyond n times. This prevents accidental calling of a third-party over some limit, for example to avoid surpassing the service amount that you have purchased.

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

Table 12 Additional REST Provider Parameter Details

Parameter Description
max_tokens Maximum number of tokens in the output text.
temperature

Degree of randomness used when generating the output text, in the range of 0.0-5.0.

To generate the same output for a prompt, use 0. To generate a random new text for that prompt, increase the temperature.

Note: Start with the temperature set to 0. If you do not require random results, a recommended temperature value is between 0 and 1. A higher value is not recommended because a high temperature may produce creative text, which might also include hallucinations.

topP

Probability of tokens in the output, in the range of 0.0-1.0.

A lower value provides less random responses and a higher value provides more random responses.
candidateCount Number of response variations to return, in the range of 1-4.
maxOutputTokens Maximum number of tokens to generate for each response.

Let us look at some example configurations for all third-party providers:

Important:

Cohere example:

{
  "provider"       : "cohere",
  "credential_name": "COHERE_CRED",
  "url"            : "https://api.cohere.example.com/chat",
  "model"          : "command"
}

OCI Generative AI (On-Demand mode) example:

Note: For Generative AI, if you want to pass any additional REST provider-specific parameters, then you must enclose those in chatRequest.

{
  "provider": "ocigenai",
  "credential_name" : "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
  "model": "meta.llama-3.3-70b-instruct",
  "chatRequest": {
    "maxTokens": 256
  }
}

OCI Generative AI (Dedicated mode) examples:

For Cohere models, use "apiFormat":"COHERE"

{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
  "model": "ocid1.generativeaiendpoint.oc1.us-chicago-1...",
  "chatRequest": {
    "apiFormat":"COHERE"
  }
}

For other models, use "apiFormat":"GENERIC"

{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
  "model": "ocid1.generativeaiendpoint.oc1.us-chicago-1...",
  "chatRequest": {
    "apiFormat":"GENERIC"
  }
}

Note: For Dedicated mode, specify the model OCID in the model parameter instead of the model name.

Google AI example:

{
  "provider"        : "googleai",
  "credential_name" : "GOOGLEAI_CRED",
  "url"             : "https://googleapis.example.com/models/",
  "model"           : "gemini-pro:generateContent"
}

Hugging Face example:

{
  "provider"        : "huggingface",
  "credential_name" : "HF_CRED",
  "url"             : "https://api.huggingface.example.com/models/",
  "model"           : "gpt2"
}

Ollama example:

{
  "provider"       : "ollama",
  "host"           : "local",
  "url"            : "http://localhost:11434/api/generate",
  "model"          : "phi3:mini"
}

OpenAI example:

{
  "provider"        : "openai",
  "credential_name" : "OPENAI_CRED",
  "url"             : "https://api.openai.example.com/v1/chat/completions",
  "model"           : "gpt-4o-mini",
  "max_tokens"      : 60,
  "temperature"     :
1.0
}

OpenAI-compatible provider examples:

Note: This is just one example of how to set up the configuration for a number of OpenAI-compatible third-party providers, such as Llamafile, vLLM, and xAI. The provider value must specify openai while the url value depends on your chosen third-party provider’s REST endpoint.

Set host to local to disable credential. Set model to the desired model if the third-party provider requires a model name (for example, Ollama, vLLM, and so on). Otherwise, you can set model to any string such as any (for example, if using Llamafile).

{
  "provider": "openai",
  "url": "http://localhost:8080/v1/chat/completions",
  "host": "local",
  "model": "any"
}
{
   "provider": "openai",
   "credential_name": "XAI_CRED",
   "url": "https://api.x.ai/v1/chat/completions",
   "model": "grok-4"
}

Vertex AI example:

{
  "provider"         : "vertexai",
  "credential_name"  : "VERTEXAI_CRED",
  "url"              : "https://googleapis.example.com/models/",
  "model"            : "gemini-1.0-pro:generateContent",
  "generation_config": {
                        "temperature"    : 0.9,
                        "topP"           : 1,
                        "candidateCount" : 1,
                        "maxOutputTokens": 256
                       }
}

Mistral AI example:

{
   "provider": "mistralai",
   "credential_name": "MISTRALAI_CRED",
   "url": "https://api.mistral.ai/v1/chat/completions",
   "model": "mistral-small-latest"
}

Anthropic example:

{
   "provider": "anthropic",
   "credential_name": "ANTHROPIC_CRED",
   "url": "https://api.anthropic.com/v1/messages",
   "model": "claude-3-opus-20240229",
   "max_tokens": 256
}

Examples

UTL_TO_TEXT

Use the DBMS_VECTOR_CHAIN.UTL_TO_TEXT chainable utility function to convert an input document (for example, PDF, DOC, JSON, XML, or HTML) to plain text.

Purpose

To perform a file-to-text transformation by using the Oracle Text component (CONTEXT) of Oracle AI Database.

Syntax

DBMS_VECTOR_CHAIN.UTL_TO_TEXT (
    DATA          IN CLOB | BLOB,
    PARAMS        IN JSON default NULL
) return CLOB;

DATA

This function accepts the input data type as CLOB or BLOB. It can read documents from a remote location or from files stored locally in the database tables.

It returns a plain text version of the document as CLOB.

Oracle Text supports around 150 file types. For a complete list of all the supported document formats, see Oracle Text Reference.

PARAMS

Specify the following input parameter in JSON format:

{
    "plaintext": "true or false",
    "charset": "UTF8 | EUCJP | <other_valid_charset>",
    "format": "BINARY | TEXT | IGNORE"
}

Table 13 Parameter Details

Parameter Description
plaintext

Plain text output.

The default value for this parameter is true, that is, by default the output format is plain text.

If you do not want to return the document as plain text, then set this parameter to false. When you set it to false, then the document is returned as HTML markup instead of plain text.
charset Character set encoding. The default value for this parameter is the current database character set. That is, by default, the input is assumed to use the same character set as the database. If your input uses a different character set, specify that character set using this parameter.
format Format type of the content to be processed. Valid values are:
  • BINARY: For rich content like PDF/Word. The default value of the format parameter.
  • TEXT: For plain text
  • IGNORE: No conversion

Example

select DBMS_VECTOR_CHAIN.UTL_TO_TEXT (
    t.blobdata,
    json('{
            "plaintext": "true",
            "charset"  : "UTF8",
            "format"   : "TEXT"
          }')
) from tab t;

End-to-end example:

To run an end-to-end example scenario using this function, see Convert File to Text to Chunks to Embeddings Within Oracle AI Database.

UTL_TO_SUMMARY

Use the DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY chainable utility function to generate a summary for textual documents.

A summary is a short and concise extract with key features of a document that best represents what the document is about as a whole. A summary can be free-form paragraphs or bullet points based on the format that you specify.

Purpose

To perform a text-to-summary transformation by accessing either Oracle AI Database or a third-party service provider:

Note: Currently, UTL_TO_SUMMARY does not work for Generative AI because the model and summary endpoint supported for Generative AI have been retired. It will be available in a subsequent release.

WARNING:

Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.

Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle’s responsibility or liability for any damages resulting from such access.

Syntax

DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY (
    DATA          IN CLOB,
    PARAMS        IN JSON default NULL
) return CLOB;

DATA

This function accepts the input data type in plain text as CLOB.

It returns a summary of the input document also as CLOB.

PARAMS

Specify summary parameters in JSON format, depending on the service provider that you want to use for document summarization.

If using Oracle AI Database as the provider:

{
  "provider"     : "database",
  "glevel"       : "<summary format>",
  "numParagraphs": <number in the range 1-16>,
  "maxPercent"   : <number in the range 1-100>,
  "num_themes"   : <number in the range 1-50>,
  "language"     : "<name of the language>"
}

Table 14 Database Provider Parameter Details

Parameter Description
provider Specify database (default setting) to access Oracle AI Database as the provider. Leverages the document gist or summary generated by Oracle Text.
glevel

Format to display the summary:

  • SENTENCE | S: As a list of sentences

  • PARAGRAPH | P: In a free-form paragraph

numParagraphs

Maximum number of document paragraphs (or sentences) selected for the summary. The default value is 16.

The numParagraphs parameter is used only when this parameter yields a smaller summary size than the summary size yielded by the maxPercent parameter, because the function always returns the smallest size summary.

maxPercent

Maximum number of document paragraphs (or sentences) selected for the summary, as a percentage of the total paragraphs (or sentences) in the document. The default value is 10.

The maxPercent parameter is used only when this parameter yields a smaller summary size than the summary size yielded by the numParagraphs parameter, because the function always returns the smallest size summary.

num_themes

Number of theme summaries to produce. For example, if you specify 10, then this function returns the top 10 theme summaries. If you specify 0 or NULL, then this function returns all themes in a document.

The default value is 50. If the document contains more than 50 themes, only the top 50 themes show conceptual hierarchy.

language Language name of your summary text, as listed in Supported Languages and Data File Locations.

For example:

{
  "provider"          : "database",
  "glevel"            : "sentence",
  "numParagraphs"     : 1
}

If using a third-party provider:

Set the following parameters along with additional summarization parameters specific to your provider:

{
  "provider"          : "<AI service provider>",
  "credential_name"   : "<credential name>",
  "url"               : "<REST endpoint URL for summarization service>",
  "model"             : "<REST provider summarization model name>",
  "transfer_timeout"  : <maximum wait time for the request to complete>,
  "max_count": "<maximum calls to the AI service provider>",
  "<additional REST provider parameter>": "<REST provider parameter value>"
}

Table 15 Third-Party Provider Parameter Details

Parameter Description
provider

Third-party service provider that you want to access to get the summary. A REST call is made to the specified provider to access its text summarization model.

Specify one of the following values:

  • cohere

  • googleai

  • huggingface

  • ocigenai

  • openai

  • vertexai

  • mistralai

  • anthropic
credential_name

Name of the credential in the form:

schema.credential_name

A credential name holds authentication credentials to enable access to your provider for making REST API calls.

You need to first set up your credential by calling the DBMS_VECTOR_CHAIN.CREATE_CREDENTIAL helper function to create and store a credential, and then refer to the credential name here. See CREATE_CREDENTIAL.

url URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
model

Name of the third-party text summarization model in the form:

schema.model_name

If the model name is not schema-qualified, then the schema of the procedure invoker is used.

Note: For Generative AI, you must specify schema.model_name. All the third-party models supported for Generative AI are listed in Supported Third-Party Provider Operations and Endpoints.

transfer_timeout

Maximum time to wait for the request to complete.

The default value is 60 seconds. You can increase this value for busy web servers.

max_count

Maximum number of times the API can be called for a given third-party provider.

When set to an integer n, max_count stops the execution of the API for the given provider beyond n times. This prevents accidental calling of a third-party over some limit, for example to avoid surpassing the service amount that you have purchased.

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

Table 16 Additional REST Provider Parameter Details

Parameter Description
length

Approximate length of the summary text:

  • SHORT: Roughly up to 2 sentences

  • MEDIUM: Between 3 and 5 sentences

  • LONG: 6 or more sentences

  • AUTO: The model chooses a length based on the input size

Note: For Generative AI, you must enter this value in uppercase.

format

Format to display the summary:

  • PARAGRAPH: In a free-form paragraph

  • BULLETS: In bullet points

Note: For Generative AI, you must enter this value in uppercase.

temperature

Degree of randomness used when generating output text, in the range of 0.0-5.0.

To generate the same output for a prompt, use 0. To generate a random new text for that prompt, increase the temperature.

Default temperature is 1 and the maximum temperature is 5.

Note: To summarize a text, start with the temperature set to 0. If you do not require random results, a recommended temperature value is 0.2 for Generative AI and between 0 and 1 for Cohere. Use a higher value if for example you plan to perform a selection of the various summaries afterward. Do not use a high temperature for summarization because a high temperature encourages the model to produce creative text, which might also include hallucinations.
extractiveness

How much to reuse the input in the summary:

  • LOW: Summaries with low extractiveness tend to paraphrase.

  • HIGH: Summaries with high extractiveness lean toward reusing sentences verbatim.

Note: For Generative AI, you must enter this value in uppercase.

max_tokens Maximum number of tokens in the output text.
topP

Probability of tokens in the output, in the range of 0.0-1.0.

A lower value provides less random responses and a higher value provides more random responses.
candidateCount Number of response variations to return, in the range of 1-4.
maxOutputTokens Maximum number of tokens to generate for each response.

Note: When specifying the length, format, and extractiveness parameters for Generative AI, ensure to enter the values in uppercase letters.

Let us look at some example configurations for all third-party providers:

Important:

Cohere example:

{
  "provider"          : "cohere",
  "credential_name"   : "COHERE_CRED",
  "url"               : "https://api.cohere.example.com/summarize",
  "model"             : "command",
  "length"            : "medium",
  "format"            : "paragraph",
  "temperature"       :
1.0
}

Generative AI example:

{
 "provider"           : "ocigenai",
 "credential_name"    : "OCI_CRED",
 "url"                : "https://generativeai.oci.example.com/summarizeText",
 "model"              : "cohere.command-r-16k",
 "length"             : "MEDIUM",
 "format"             : "PARAGRAPH"
}

Google AI example:

{
  "provider"          : "googleai",
  "credential_name"   : "GOOGLEAI_CRED",
  "url"               : "https://googleapis.example.com/models/",
  "model"             : "gemini-pro:generateContent",
  "generation_config" : {
    "temperature"     : 0.9,
    "topP"            : 1,
    "candidateCount"  : 1,
    "maxOutputTokens" : 256
  }
}

Hugging Face example:

{
  "provider"          : "huggingface",
  "credential_name"   : "HF_CRED",
  "url"               : "https://api.huggingface.example.co/models/",
  "model"             : "facebook/bart-large-cnn"
}

OCI Generative AI (ocigenai) example

{
  "provider": "ocigenai",
  "credential_name" : "OCI_CRED",
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
  "model": "meta.llama-3.3-70b-instruct",
  "temperature": "0.0",
  "length": "SHORT",
  "format": "PARAGRAPH",
  "chatRequest": {
    "maxTokens": 256
  }
}

Ollama example:

{
  "provider"          : "ollama",
  "host"              : "local",
  "url"               : "http://localhost:11434/api/generate",
  "model"             : "phi3:mini"
}

OpenAI example:

{
  "provider"          : "openai",
  "credential_name"   : "OPENAI_CRED",
  "url"               : "https://api.openai.example.com/v1/chat/completions",
  "model"             : "gpt-4o-mini",
  "max_tokens"        : 256,
  "temperature"       : 1.0
}

OpenAI-compatible provider examples:

Note: This is just one example of how to set up the configuration for a number of OpenAI-compatible third-party providers, such as Llamafile, vLLM, and xAI. The provider value must specify openai while the url value depends on your chosen third-party provider’s REST endpoint.

Set host to local to disable credential. Set model to the desired model if the third-party provider requires a model name (for example, Ollama, vLLM, and so on). Otherwise, you can set model to any string such as any (for example, if using Llamafile).

{
  "provider": "openai",
  "url": "http://localhost:8080/v1/chat/completions",
  "host": "local",
  "model": "any"
}
{
   "provider": "openai",
   "credential_name": "XAI_CRED",
   "url": "https://api.x.ai/v1/chat/completions",
   "model": "grok-4"
}

Vertex AI example:

{
  "provider"          : "vertexai",
  "credential_name"   : "VERTEXAI_CRED",
  "url"               : "https://googleapis.example.com/models/",
  "model"             : "gemini-1.0-pro:generateContent",
  "generation_config" : {
    "temperature"     : 0.9,
    "topP"            : 1,
    "candidateCount"  : 1,
    "maxOutputTokens" : 256
  }
}

Mistral AI example:

{
   "provider": "mistralai",
   "credential_name": "MISTRALAI_CRED",
   "url": "https://api.mistral.ai/v1/chat/completions",
   "model": "mistral-small-latest"
}

Anthropic example:

{
   "provider": "anthropic",
   "credential_name": "ANTHROPIC_CRED",
   "url": "https://api.anthropic.com/v1/messages",
   "model": "claude-3-opus-20240229",
   "max_tokens": 256
}

Examples

Supported Languages and Data File Locations

These are the supported languages for which language data files are distributed by default in the specified directories.

Language Name Abbreviation Data File
AFRIKAANS af ctx/data/eos/dreosaf.txt
AMERICAN us ctx/data/eos/dreosus.txt
ARABIC ar ctx/data/eos/dreosar.txt
BASQUE eu ctx/data/eos/dreoseu.txt
BELARUSIAN be ctx/data/eos/dreosbe.txt
BRAZILIAN PORTUGUESE ptb ctx/data/eos/dreosptb.txt
BULGARIAN bg ctx/data/eos/dreosbg.txt
CANADIAN FRENCH frc ctx/data/eos/dreosfrc.txt
CATALAN ca ctx/data/eos/dreosca.txt
CROATIAN hr ctx/data/eos/dreoshr.txt
CYRILLIC SERBIAN csr ctx/data/eos/dreoscsr.txt
CZECH cs ctx/data/eos/dreoscs.txt
DANISH dk ctx/data/eos/dreosdk.txt
DARI prs ctx/data/eos/dreosprs.txt
DUTCH nl ctx/data/eos/dreosnl.txt
EGYPTIAN eg ctx/data/eos/dreoseg.txt
ENGLISH gb ctx/data/eos/dreosgb.txt
ESTONIAN et ctx/data/eos/dreoset.txt
FINNISH sf ctx/data/eos/dreossf.txt
FRENCH f ctx/data/eos/dreosf.txt
GALICIAN ga ctx/data/eos/dreosga.txt
GERMAN d ctx/data/eos/dreosd.txt
GERMAN DIN din ctx/data/eos/dreosdin.txt
GREEK el ctx/data/eos/dreosel.txt
HEBREW iw ctx/data/eos/dreosiw.txt
HINDI hi ctx/data/eos/dreoshi.txt
HUNGARIAN hu ctx/data/eos/dreoshu.txt
ICELANDIC is ctx/data/eos/dreosis.txt
INDONESIAN in ctx/data/eos/dreosin.txt
ITALIAN i ctx/data/eos/dreosi.txt
JAPANESE ja ctx/data/eos/dreosja.txt
KOREAN ko ctx/data/eos/dreosko.txt
LATIN AMERICAN SPANISH esa ctx/data/eos/dreosesa.txt
LATIN BOSNIAN lbs ctx/data/eos/dreoslbs.txt
LATIN SERBIAN lsr ctx/data/eos/dreoslsr.txt
LATVIAN lv ctx/data/eos/dreoslv.txt
LITHUANIAN lt ctx/data/eos/dreoslt.txt
MACEDONIAN mk ctx/data/eos/dreosmk.txt
MALAY ms ctx/data/eos/dreosms.txt
MEXICAN SPANISH esm ctx/data/eos/dreosesm.txt
NORWEGIAN n ctx/data/eos/dreosn.txt
NYNORSK nn ctx/data/eos/dreosnn.txt
PERSIAN fa ctx/data/eos/dreosfa.txt
POLISH pl ctx/data/eos/dreospl.txt
PORTUGUESE pt ctx/data/eos/dreospt.txt
ROMANIAN ro ctx/data/eos/dreosro.txt
RUSSIAN ru ctx/data/eos/dreosru.txt
SIMPLIFIED CHINESE zhs ctx/data/eos/dreoszhs.txt
SLOVAK sk ctx/data/eos/dreossk.txt
SLOVENIAN sl ctx/data/eos/dreossl.txt
SPANISH e ctx/data/eos/dreose.txt
SWEDISH s ctx/data/eos/dreoss.txt
THAI th ctx/data/eos/dreosth.txt
TRADITIONAL CHINESE zht ctx/data/eos/dreoszht.txt
TURKISH tr ctx/data/eos/dreostr.txt
UKRAINIAN uk ctx/data/eos/dreosuk.txt
URDU ur ctx/data/eos/dreosur.txt

Related Topics