1. PL/SQL Packages and Types Reference
  2. DBMS_VECTOR

205 DBMS_VECTOR

The DBMS_VECTOR package simplifies common operations with Oracle AI Vector Search, such as chunking and embedding data, generating text for prompts, or creating vector indexes.

These functions accept their respective input parameters in JSON format.

Related Topics

Summary of DBMS_VECTOR Subprograms

This table lists the DBMS_VECTOR subprograms and briefly describes them.

Table 205-1 DBMS_VECTOR Package Subprograms

Subprogram Description

ONNX Model Related Procedures:

These procedures enable you to load an ONNX model into Oracle Database and drop the ONNX model.

LOAD_ONNX_MODEL

Loads an ONNX model into the database

DROP_ONNX_MODEL Procedure

Drops the ONNX model

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_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_GENERATE_TEXT

Generates text for a prompt or input string

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

Data Access Functions:

These functions enable you to retrieve data, create index, and perform simple similarity search operations.

CREATE_INDEX

Creates a vector index

REBUILD_INDEX

Rebuilds a vector index

QUERY

Perform a similarity search query

Accuracy Reporting Function:

These functions enable you to determine the accuracy of existing search indexes and to capture accuracy values achieved by approximate searches performed by past workloads.

INDEX_ACCURACY_QUERY

Verifies the accuracy of a vector index
INDEX_ACCURACY_REPORT Captures accuracy values achieved by approximate searches

CREATE_CREDENTIAL

Use the DBMS_VECTOR.CREATE_CREDENTIAL credential helper procedure to create a credential name for storing user authentication details in Oracle 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:

If you are using Oracle Database as the service provider, then you do not need to create a credential.

Syntax

DBMS_VECTOR.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 205-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

  • For Generative AI:
    declare
  jo json_object_t;
begin
  jo := json_object_t();
  jo.put('user_ocid','ocid1.user.oc1..aabbalbbaa1112233aabbaabb1111222aa1111bb');
  jo.put('tenancy_ocid','ocid1.tenancy.oc1..aaaaalbbbb1112233aaaabbaa1111222aaa111a');
  jo.put('compartment_ocid','ocid1.compartment.oc1..ababalabab1112233abababab1111222aba11ab');
  jo.put('private_key','AAAaaaBBB11112222333...AAA111AAABBB222aaa1a/+');
  jo.put('fingerprint','01:1a:a1:aa:12:a1:12:1a:ab:12:01:ab:a1:12:ab:1a');
  dbms_output.put_line(jo.to_string);
  dbms_vector.create_credential(
    credential_name   => 'OCI_CRED',
    params            => json(jo.to_string));
end;
/
  • For Cohere:
    declare
  jo json_object_t;
begin
  jo := json_object_t();
  jo.put('access_token', 'A1Aa0abA1AB1a1Abc123ab1A123ab123AbcA12a');
  dbms_vector.create_credential(
    credential_name   => 'COHERE_CRED',
    params            => json(jo.to_string));
end;
/

To run an end-to-end example scenario using this function, see Directly Convert Text String to Embedding in Oracle Database AI Vector Search User's Guide.

CREATE_INDEX

Use the DBMS_VECTOR.CREATE_INDEX procedure to create an index.

Syntax

DBMS_VECTOR.CREATE_INDEX (
    IDX_NAME                    IN VARCHAR2,
    TABLE_NAME                  IN VARCHAR2,
    IDX_VECTOR_COL              IN VARCHAR2,
    IDX_INCLUDE_COLS            IN VARCHAR2,
    IDX_PARTITIONING_SCHEME     IN VARCHAR2,
    IDX_ORGANIZATION            IN VARCHAR2,
    IDX_DISTANCE_METRIC         IN VARCHAR2,
    IDX_ACCURACY                IN NUMBER,
    IDX_PARAMETERS              IN CLOB,
    IDX_PARALLEL_CREATION       IN NUMBER,
    IDX_DDL                     IN CLOB
);

Parameters

Specify the input parameters in JSON format.

Table 205-3 CREATE_INDEX (IN) Parameters of DBMS_VECTOR

Parameter Description

table_name

Table on which to create the index.

idx_name

Name of the index.

idx_vector_col

Vector column on which to create the index.

idx_partitioning_scheme

Partitioning scheme. LOCAL or GLOBAL.

idx_organization

Index organization. Either NEIGHBOR PARTITIONS or INMEMORY NEIGHBOR GRAPH.

idx_distance_metric

Distance computation metric. Defaults to COSINE. Can also be MANHATTAN, HAMMING, DOT, EUCLIDEAN, L2_SQUARED, EUCLIDEAN_SQUARED.

idx_accuracy

Target accuracy.

idx_parameters

Takes in CLOB with type of index and associated parameters. Type, neighbor partitions for IVF, neighbors/efConstruction for HNSW)

.

idx_parallel_creation

Number of parallel threads used for index construction.

idx_dd

Defaults to NULL. Only passed by rebuild_index() to rebuild the index.

DROP_CREDENTIAL

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

Syntax

DBMS_VECTOR.DROP_CREDENTIAL (
    CREDENTIAL_NAME      IN VARCHAR2
);

CREDENTIAL_NAME

Specify the credential name that you want to drop.

Examples

  • For Generative AI:
    exec dbms_vector.drop_credential('OCI_CRED');
  • For Cohere:
    exec dbms_vector.drop_credential('COHERE_CRED');

DROP_ONNX_MODEL Procedure

This procedure deletes the specified ONNX model.

Syntax

DBMS_DATA_MINING.DROP_ONNX_MODEL (model_name IN VARCHAR2,
                                  force      IN BOOLEAN DEFAULT FALSE);

Parameters

Table 205-4 DROP_MODEL Procedure Parameters

Parameter Description

model_name

Name of the machine learning ONNX model in the form [schema_name.]model_name. If you do not specify a schema, then your own schema is used.

force

Forces the machine learning ONNX model to be dropped even if it is invalid. An ONNX model may be invalid if a serious system error interrupted the model build process.

Usage Note

To drop a machine learning model, you must be the owner or you must have the DROP ANY MINING MODEL privilege. See Oracle Data Mining User's Guide for information about privileges for Oracle Machine Learning for SQL.

Example

You can use the following command to delete a valid ONNX model named doc_model that exists in your schema. 

BEGIN
  DBMS_DATA_MINING.DROP_ONNX_MODEL(model_name => 'doc_model');
END;
/

INDEX_ACCURACY_QUERY

Use the DBMS_VECTOR.INDEX_ACCURACY_QUERY function to verify the accuracy of a vector index for a given query vector, top-K, and target accuracy.

Syntax

DBMS_VECTOR.INDEX_ACCURACY_QUERY (
    OWNER_NAME         IN VARCHAR2,
    INDEX_NAME         IN VARCHAR2,
    QV                 IN VECTOR,
    TOP_K              IN NUMBER,
    TARGET_ACCURACY    IN NUMBER
) return VARCHAR2;

DBMS_VECTOR.INDEX_ACCURACY_QUERY (
    OWNER_NAME         IN VARCHAR2, 
    INDEX_NAME         IN VARCHAR2,
    QV                 IN VECTOR,
    TOP_K              IN NUMBER,
    QUERY_PARAM        IN JSON
) return VARCHAR2;

Parameters

Table 205-5 INDEX_ACCURACY_QUERY (IN) Parameters of DBMS_VECTOR

Parameter Description
owner_name

The name of the vector index owner.
index_name

The name of the vector index.
qv

Specifies the query vector.
top_k
The 
top_k
value for accuracy computation.
target_accuracy

The target accuracy value for the vector index.

For information about determining the accuracy of your vector indexes, see Index Accuracy Report in Oracle Database AI Vector Search User's Guide.

INDEX_ACCURACY_REPORT

Use the DBMS_VECTOR.INDEX_ACCURACY_REPORT function to capture from your past workloads, accuracy values achieved by approximate searches using a particular vector index for a certain period of time.

Syntax

DBMS_VECTOR.INDEX_ACCURACY_REPORT (
    OWNER_NAME         IN VARCHAR2,
    INDEX_NAME         IN VARCHAR2,
    START_TIME         IN TIMESTAMP WITH TIME ZONE,
    END_TIME           IN TIMESTAMP WITH TIME ZONE
) return NUMBER;

Parameters

Table 205-6 INDEX_ACCURACY_REPORT (IN) Parameters of DBMS_VECTOR

Parameter Description
owner_name

The name of the vector index owner.
index_name

The name of the vector index.
start_time

Specifies from what time to capture query vectors to consider for the accuracy computation. A NULL start_time uses query vectors captured in the last 24 hours.

end_time

Specifies an end point up until which query vectors are considered for accuracy computation. A NULL end_time uses query vectors captured from start_time until the current time.

For information about determining the accuracy of your vector indexes, see Index Accuracy Report in Oracle Database AI Vector Search User's Guide.

LOAD_ONNX_MODEL

This procedure enables you to load an ONNX model into the Database.

Syntax

DBMS_VECTOR.LOAD_ONNX_MODEL (
     directory  VARCHAR2,
     file_name   VARCHAR2,
     model_name  VARCHAR2,
     metadata   JSON);
DBMS_VECTOR.LOAD_ONNX_MODEL(
model_name  IN  VARCHAR2,
model_data  IN  BLOB,
metadata    IN  JSON);

Parameters

Table 205-7 LOAD_ONNX_MODEL Procedure Parameters

Parameter Description

directory

The directory name of the data dump. For example, DM_DUMP.

file_name

A VARCHAR2 type parameter that specifies the name of the ONNX model.

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, then your own schema is used.

model_data

It is a BLOB holding the ONNX representation of the model. The BLOB contains the identical byte sequence as the one stored in an ONNX file.

metadata

 A JSON description of the metadata describing the model. The metadata at minimum must describe the machine learning function supported by the model. The model's metadata parameters are described in JSON Metadata Parameters for ONNX Models.

Examples

The following examples illustrates a code snippet of using the DBMS_VECTOR.LOAD_ONNX_MODEL procedure. The complete step-by-step example is illustrated in Import ONNX Models and Generate Embeddings. 

EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(
	'DM_DUMP', 
	'my_embedding_model.onnx', 
	'doc_model', 
	JSON('{"function" : "embedding", "embeddingOutput" : "embedding", "input": {"input": ["DATA"]}}'));
DBMS_VECTOR.LOAD_ONNX_MODEL('my_embedding_model.onnx',
                                             :blob_bind_variable, 
                                              JSON('{"function" : "embedding", 
                                                     "embeddingOutput" : "embedding" ,
                                                      "input":{"input": ["DATA"]}}'));

For a complete example to illustrate how you can define a BLOB variable and use it in the LOAD_ONNX_MODEL procedure, you can have the following: 

CREATE OR REPLACE MY_LOAD_EMBEDDING_MODEL(embedding_model_name VARCHAR2, onnx_blob BLOB) IS 
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(embedding_model_name,
                            onnx_blob, 
                            JSON('{"function" : "embedding", 
                                   "embeddingOutput" : "embedding" ,
                                   "input":{"input": ["DATA"]}}'));
END;
/

Usage Notes

The name of the model follows the same restrictions as those used for other machine learning models, namely:

  • The schema name, if provided, is limited to 128 characters.
  • The model name is limited to 123 characters and must follow the rules of unquoted identifiers: they contain only alphanumeric characters, the underscore (_), dollar sign ($), and pound sign (#). The initial character must be alphabetic.
  • The model size is limited to 1 gigabyte.
  • The model must not depend on external initializers. To know more about initializers and other ONNX concepts, see https://onnx.ai/onnx/intro/concepts.html.

  • There are default input and output names for input and output attributes for models that are prepared by the Python utility. You can load those models without the JSON parameters. For example: 

    EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL('DM_DUMP', 'my_embedding_model.onnx', 'doc_model'));

See Also:

Oracle Machine Learning for SQL User’s Guide for examples of using ONNX models for machine learning tasks
JSON Metadata Parameters for ONNX Models

When importing models using the DBMS_DATA_MINING.IMPORT_ONNX_MODEL procedure, you supply metadata as JSON parameters.

Parameters

Field Value Type Description
function String

Specify regression, classification, clustering, or embedding. This is a mandatory setting.

NOTE: The only JSON parameter required when importing the model is the machine learning function.

input NA Describes the model input mapping. See "Input" in Usage Notes.
regressionOutput String The name of the regression model output that stores the regression results. The output is expected to be a tensor of supported shape of any supported regression output type. See "Output" in Usage Notes.
classificationProbOutput String The name of the classification model output storing probabilities. The output is expected to be a tensor value of type float (width 32/64) of supported shape. See "Automatic normalization of output probabilities" in Usage Notes.
clusteringDistanceOutput String The name of the clustering model output storing distances. The output is of type float (width 16/32/64) of supported shape.
clusteringProbOutput String The name of the clustering model output storing probabilities. The output is of type float (width 16/32/64) of supported shape.
classificationLabelOutput String

The name of the model output holding label information.

You have the following metadata parameters to specify the labels for classification:

  • labels: specify the labels directly in the JSON metadata
  • classificationLabelOutput: specify the model output that provides labels

If you do not specify any value for this parameter or the function of the model is not classification, you will receive an error.

The user can specify to use labels from the model directly by setting classificationLabelOutput to the model output holding the label information. The tensor output holding the label information must be the same size as the number of classes and must be of integer or string type. If the tensor that holds the labels is of string type, the returned type of the PREDICTION operator is VARCHAR2. If the tensor that holds the labels is of integer type, the returned type of the PREDICTION operator is NUMBER.

normalizeProb String Describes automatic normalization of output probabilities. See "Automatic normalization of output probabilities" in Usage Notes.
labels NA

The labels used for classification.

If you want to use custom labels, specify the labels using the labels field in the JSON metadata. The field can be set to an array of length equal to the number of classes. The labels for the class i must be stored at index i of the label array. If an array of strings is used, the returned type of the PREDICTION operator is VARCHAR2. The size of the string labels specified by the user cannot exceed 4000 bytes. If an array of numbers is used, the returned type of the PREDICTION operator is NUMBER.

If you do not specify labels or classificationLabelOutput, classes are identified by integers in the range 1 to N where N is the number of classes. In this case, the returned type of the PREDICTION operator is NUMBER.

embeddingOutput String The model output that holds the generated embeddings.
suitableDistanceMetrics String An array of names of suitable distance metrics for the model. The names must be the names of the distance metrics used for the Oracle VECTOR_DISTANCE operator. To know the supported distance metrics, see Vector Distance Metrics.
normalization Boolean A boolean value indicates if normalization is applied to the output vector. The value 1 means normalization is applied. Normalization is process of converting an embedding vector so that it's norm or length equals 1. A normalized vector maintains its direction but its length becomes 1. The resulting vector is often called a unit vector.
maxSequenceLength Number The maximum length of the token (input) sequence that is meaningful for the model. This parameter sets a limit on the number of tokens, words, or elements in each input sequence that the model will process. This ensures uniform input size for the model. For example, the value could be 128, or 512 to 4096 depending on the task for which the parameter is used. A machine translation model might have a maxSequenceLength of 512, accommodating sentences or paragraphs up to 512 tokens for translation tasks.
pooling String Indicates the pooling function performed on the output vector.
modelDescription Object A JSON object that allows users to add additional descriptions to the models complementing the existing ONNX metadata for model description.
languages String A comma-separated list of language name or abbreviation, as described in "A.1 Languages" of Oracle Database Globalization Support Guide. If you import multi-lingual embedding model, specify the language or the language abbreviation as the metadata.
tokenizer String Tokenizers help in transforming text into words. There are several tokenizers available, including: bert, gpt2, bpe, wordpiece, sentencepiece, and clip.
embeddingLayer String An identifier for the embedding layer. An embedding layer, serving as a hidden layer in neural networks, transforms input data from high to lower dimensions, enhancing the network's understanding of input relationships and data processing efficiency. Embedding layer helps in processing and analyzing categorical or discrete data. It achieves this by transforming categories into continuous embeddings, capturing the essential semantic relationships and similarities between them. For example the last hidden state in some transformer, or a layer in a resnet network.
defaultOnNull NA

Specify the replacement of missing values in the JSON using the defaultOnNull field. If defaultOnNull is not specified, the replacement of missing values is not performed. The defaultOnNull sets the missing values to NULL by default. You can override the default value of NULL by providing meaningful default values to substitute for NULL. The field must be a JSON object literal, whose fields are the input attribute names and whose values are the default values for the input. Note that the default value is of type string and must be a valid Oracle PL/SQL NVL value for the given datatype.

Note: The parameters are case-sensitive. A number of default conventions for output parameter names and default values allows to minimize the information that you may have to provide. The parameters such as suitableDistanceMetrics are informational only and you are not expected to provide this information while importing the model. The JSON descriptor may specify only one input attribute. If more are specified, you will receive an error. You will receive an error if the normalizeProb field is specified as the JSON metadata parameter.

Usage Notes

The name of the model follows the same restrictions as those used for other machine learning models, namely:

  • Input

    When importing a model from an ONNX representation, you must specify the name of the attribute used for scoring and how it maps to actual ONNX inputs. A scoring operator uses these attribute names to identify the columns to be used. (For example, PREDICTION ). Follow these conventions to specify the attribute names using the input field:

    not specified: When the field input is not specified, attribute names are mapped directly to model inputs by name. That is, if the attribute name is not specified in the JSON metadata, then the name of the input tensor is used as an attribute name. Each model input must have dimension [batch_size, value]. If you do not specify input in the JSON metatdata, the value must be 1. You don’t have to specify extra metadata if the input of the model already conforms to the format. For an embedding model, a single input is provided that may be used in batches. Here, if the input parameter is not specified in the JSON metadata, the valid model will have [batch_size, 1].

    You must ensure that all attribute names, whether implied by the model or explicitly set by you through the input field, are valid Oracle Database identifiers for column names. Each attribute name within a model must be unique, ensuring no duplicates exist.

    You can explicitly specify attribute name for model that use input tensors that have a dimension larger than 1 (for example, (batch_size, 2)). In this case, you must specify a name for each of these values for them to be interpreted as independent attribute name. This can be done for regression, classification, clustering which are models whose scoring operation can take multiple input attributes.

  • Output

    As models might have multiple outputs, you can specify which output is of interest for a specific machine learning technique. You have the following ways to specify model outputs:

    • Specify the output name of interest in the JSON during model import. If the specified name is not a valid model output (see the table with valid outputs for a given machine learning function), you will receive an error.
    • If the model produces an output that matches the expected output name for the given machine learning technique (for example, classificationProbOutput) and you didn't explicitly specify it, the output is automatically assumed.
    • If you do not specify any output name and the model has a single output, the system assumes that the single output corresponds to a default specific to the machine learning technique. For an embedding machine learning function, the default value is embeddingOutput.

      The system reports an error if you do not specify model outputs or if you supply outputs that the specified machine learning function does not support. The following table displays supported outputs for a specific machine learning function:

      Machine learning function Output
      regression regressionOutput
      classification classificationProbOutput
      clustering clusteringDistanceOutput
      embedding embeddingOutput

      If none of the mentioned model outputs are specified, or if you supply outputs that are not supported by the specified machine learning function, you will receive an error.

  • Automatic Normalization of Output Probabilities

    Many users widely employ the softmax function to normalize the output of multi-class classification models, as it enables to easily interpret the results of these models. The softmax function is a mathematical function that converts a vector of real numbers into a probability distribution. It is also known as the softargmax, or normalized exponential function. This function is available to you to specify at the model import-time that a softmax normalization must be applied to the tensor holding output probabilities such as classificationProbOutput and clusteringProbOutput. Specify normalizeProb to define the normalization that must be applied for softmax normalization. The default setting is none, indicating that no normalization is applied. You can choose softmax to apply a softmax function to the probability output. Specifying any other value for this field will result in an error during import. Additionally, specifying this field for models other than classification and clustering will also lead to an error.

Example: Specifying JSON Metadata Parameters for Embedding Models

The following example illustrates a simple case of how you can specify JSON metadata parameters while importing an ONNX embedding model into the Database using the DBMS_DATA_MINING.IMPORT_ONNX_MODEL procedure. 

DBMS_DATA_MINING.IMPORT_ONNX_MODEL('my_embedding_model.onnx', 'doc_model', 
                JSON('{"function" : "embedding", 
                      "embeddingOutput" : "embedding" , 
                       "input":{"input": ["DATA"]}}'));

Example: Specifying Complete JSON Metadata Parameters for Embedding Models

The following example illustrates how to provide a complete JSON metadata parameters, with an exception of embeddingLayer, for importing embedding models. 

DECLARE
  metadata JSON;
  mdtxt varchar2(4000);
BEGIN
  metadata := JSON(q'#
           {
             "function"                : "embedding",
             "embeddingOutput"         : "embedding",
             "input"                   : { "input" : ["txt"]},
             "maxSequenceLength"       : 512,
             "tokenizer"               : "bert",
             "suitableDistanceMetrics" : [ "DOT", "COSINE", "EUCLIDEAN"],
             "pooling"                 : "Mean Pooling",
             "normalization"           : true,
             "languages"               : ["US"],
             "modelDescription"        : {
                 "description" : "This model was tuned for semantic search: Given a query/question, if can find relevant passages. It was trained on a large and diverse set of (question, a
nswer) pairs.", 
                 "url" : "https://example.co/sentence-transformers/my_embedding_model"}
           }
           #');
  -- load the onnx model
    DBMS_DATA_MINING.IMPORT_ONNX_MODEL('my_embedding_model.onnx', 'doc_model', metadata);
END;
/

See Also:

Oracle Machine Learning for SQL User’s Guide for examples of using ONNX models for machine learning tasks

QUERY

Use the DBMS_VECTOR.QUERY function to perform a similarity search operation which returns the top-k results as a JSON array.

Syntax

Query is overloaded and supports a version with query_vector passed in as a VECTOR type in addition to CLOB. 

DBMS_VECTOR.QUERY (
    TAB_NAME             IN VARCHAR2,
    VEC_COL_NAME         IN VARCHAR2,
    QUERY_VECTOR         IN CLOB,
    TOP_K                IN NUMBER,
    VEC_PROJ_COLS        IN JSON_ARRAY_T DEFAULT NULL,
    IDX_NAME             IN VARCHAR2 DEFAULT NULL, 
    DISTANCE_METRIC      IN VARCHAR2 DEFAULT 'EUCLIDEAN', 
    USE_INDEX            IN BOOLEAN DEFAULT 'TRUE', 
    ACCURACY             IN NUMBER DEFAULT '95', 
    IDX_PARAMETERS       IN CLOB DEFAULT NULL,
) return JSON_ARRAY_T;

DBMS_VECTOR.QUERY (
    TAB_NAME             IN VARCHAR2,
    VEC_COL_NAME         IN VARCHAR2,
    QUERY_VECTOR         IN VECTOR,
    TOP_K                IN NUMBER, 
    VEC_PROJ_COLS        IN JSON_ARRAY_T,
    IDX_NAME             IN VARCHAR2,
    DISTANCE_METRIC      IN VARCHAR2,
    USE_INDEX            IN BOOLEAN,
    ACCURACY             IN NUMBER,
    IDX_PARAMETERS       IN CLOB
) return JSON_ARRAY_T;

Parameters

Specify the input parameters in JSON format.

Table 205-8 DBMS_VECTOR.QUERY Parameters

Parameter Description

tab_name

Table name to query

vec_col_name

Vector column name

query_vector

Query vector passed in as CLOB or VECTOR.

top_k

Number of results to be returned.

vec_proj_cols

Columns to be projected as part of the result.

idx_name

Name of the index queried.

distance_metric

Distance computation metric. Defaults to COSINE. Can also be MANHATTAN, HAMMING, DOT, EUCLIDEAN, L2_SQUARED, EUCLIDEAN_SQUARED.

.

use_index

Specifies whether the search is an approximate search or exact search. Defaults to TRUE (that is, approximate).

accuracy

Specifies the minimum desired query accuracy.

idx_parameters

Specifies values of efsearch and neighbor partition probes passed in, formatted as JSON

DATA

This function accepts the input data type as VARCHAR2, NUMBER, JSON, BOOLEAN or CLOB.

REBUILD_INDEX

Use the DBMS_VECTOR.REBUILD_INDEX function to rebuild an index.

Syntax

This API rebuilds an index. In case only the idx_name is provided, it rebuilds the index using get_ddl. When all params are provided, it performs a drop index followed by a call to dbms_vector.create_index(). 

DBMS_VECTOR.REBUILD_INDEX (
    IDX_NAME                   IN VARCHAR2,
    TABLE_NAME                 IN VARCHAR2 DEFAULT NULL,
    IDX_VECTOR_COL             IN VARCHAR2 DEFAULT NULL, 
    IDX_INCLUDE_COLS           IN VARCHAR2 DEFAULT NULL,
    IDX_PARTITIONING_SCHEME    IN VARCHAR2 DEFAULT NULL,
    IDX_ORGANIZATION           IN VARCHAR2 DEFAULT NULL,
    IDX_DISTANCE_METRIC        IN VARCHAR2 DEFAULT 'EUCLIDEAN',
    IDX_ACCURACY               IN NUMBER DEFAULT 95,
    IDX_PARAMETERS             IN CLOB DEFAULT NULL,
    IDX_PARALLEL_CREATION      IN NUMBER DEFAULT 4,
);

Parameters

Specify the input parameters in JSON format.

Table 205-9 REBUILD_INDEX (IN) Parameters of DBMS_VECTOR

Parameter Description

table_name

Table on which to create the index.

idx_name

Name of the index.

idx_vector_col

Vector column on which to rebuild the index.

idx_partitioning_scheme

Partitioning scheme. LOCAL or GLOBAL.

idx_organization

Index organization. Either NEIGHBOR PARTITIONS or INMEMORY NEIGHBOR GRAPH.

idx_distance_metric

Distance computation metric. Defaults to COSINE. Can also be MANHATTAN, HAMMING, DOT, EUCLIDEAN, L2_SQUARED, EUCLIDEAN_SQUARED.

idx_accuracy

Target accuracy.

idx_parameters

Takes in CLOB with type of index and associated parameters. Type, neighbor partitions for IVF, neighbors/efConstruction for HNSW)

.

idx_parallel_creation

Number of parallel threads used for index construction.

DATA

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

If you specify only the idx_name value, then the function rebuilds the index using get_ddl. If you specify all the parameter values, then the function first performs a drop index operation and then calls the DBMS_VECTOR.CREATE_INDEX() function.

UTL_TO_CHUNKS

Use the DBMS_VECTOR.UTL_TO_CHUNKS chainable utility function to split a larger plain text document into smaller chunks (pieces of words, sentences, or paragraphs).

Purpose

To perform a text to chunks transformation, by internally calling the VECTOR_CHUNKS SQL function for the operation.

Syntax

DBMS_VECTOR.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,

  • chunk_id specifies the chunk ID for each chunk.

  • chunk_offset specifies the original position of each chunk in the source document, relative to the start of document which has a position of 1.

  • chunk_length specifies the character length of each chunk.

  • chunk_data displays text pieces from each chunk.

PARAMS

Specify the input parameters in JSON format:

{
    "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, ... ],
    "extended"     :     boolean
}

For example:

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

All these parameters are aligned with the VECTOR_CHUNKS SQL function, as follows:

  • by, max, overlap, split, language, and extended specify the same values as that of VECTOR_CHUNKS.

  • vocabulary is the name of the custom vocabulary; same as the vocabulary name that you specify when using the by vocabulary mode.

  • custom_list is an array of the custom split characters; same as the split by custom condition.

  • norm_options is an array of normalization options; same as the normalize parameter.

For a complete description of these parameters, see VECTOR_CHUNKS in Oracle Database SQL Language Reference.

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.utl_to_chunks(D.text,
   JSON('{ "by":"words",
           "max":"100",
           "overlap":"0",
           "split":"recursively",
           "language":"american",
           "normalize":"all" }')) C;

To run an end-to-end example scenario using this function, see Convert Text to Chunks With Custom Chunking Specifications in Oracle Database AI Vector Search User's Guide.

UTL_TO_EMBEDDING and UTL_TO_EMBEDDINGS

Use the DBMS_VECTOR.UTL_TO_EMBEDDING and DBMS_VECTOR.UTL_TO_EMBEDDINGS chainable utility functions to convert plain text to one or more vector embeddings.

Purpose

To perform a text to embedding transformation by accessing:

  • Oracle Database as the service provider: Calls the pretrained ONNX format embedding model that you have loaded into the database (default setting)

  • Third-party embedding model: Makes a REST call to your chosen third-party service provider, such as Cohere, Google AI, Hugging Face, Oracle Cloud Infrastructure (OCI) Generative AI, OpenAI, or Vertex AI

Syntax

DBMS_VECTOR.UTL_TO_EMBEDDING (
    DATA           IN CLOB,
    PARAMS         IN JSON default NULL
) return VECTOR;
DBMS_VECTOR.UTL_TO_EMBEDDINGS (
    DATA           IN VECTOR_ARRAY_T,
    PARAMS         IN JSON default NULL
) return VECTOR_ARRAY_T;

DATA

UTL_TO_EMBEDDING converts text (CLOB) to a single embedding (VECTOR).

UTL_TO_EMBEDDINGS convert an array of chunks (VECTOR_ARRAY_T) to an array of embeddings (VECTOR_ARRAY_T).

The embedding output includes:

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

  • embed_id displays the ID number of each embedding.

  • embed_data displays the input text that is transformed into embeddings.

  • embed_vector displays the generated vector representations.

PARAMS

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

If using Oracle Database as the provider:
{
  "provider": "database", 
  "model": "<pretrained ONNX embedding model file name>" 
}

Table 205-10 Database Provider Parameter Details

Parameter Description

provider

Specify DATABASE (default setting) to use Oracle Database as the provider. With this setting, you must load pretrained ONNX embedding model into the database.

model

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

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

If using a third-party provider:

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

  • For UTL_TO_EMBEDDING: 

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

  • For UTL_TO_EMBEDDINGS: 

    {
  "provider": "<AI service provider>", 
  "credential_name": "<credential name>",
  "url": "<REST endpoint URL for embedding service>", 
  "model": "<REST provider embedding model name>",
  "transfer_timeout": <maximum wait time for the request to complete>,
  "batch size": "<number of vectors to request at a time>",
  "<additional REST provider parameter>": "<REST provider parameter value>" 
}

Table 205-11 Third-Party Provider Parameter Details

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.

Specify one of the following values:

  • Cohere

  • GoogleAI

  • HuggingFace

  • OCIGenAI

  • OpenAI

  • 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.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 API endpoint for each REST call.

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 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.

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.

Additional REST provider parameters:

Cohere example:
{
  "provider": "cohere",
  "credential_name": "COHERE_CRED",
  "url": "https://api.cohere.example.com/embed",
  "model": "embed-model",
  "input_type": "search_query"
}
Google AI example:
{
  "provider": "googleai",
  "credential_name": "GOOGLEAI_CRED",
  "url": "https://googleapis.example.com/models/",
  "model": "embed-model"
}
Hugging Face example:
{
  "provider": "huggingface",
  "credential_name": "HF_CRED",
  "url": "https://api.huggingface.example.com/",
  "model": "embed-model",
  "wait_for_model": "true"
}
Generative AI example:
{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://generativeai.oci.example.com/embedText",
  "model": "embed-model",
  "batch_size": 10
}
OpenAI example:
{
  "provider": "openai",
  "credential_name": "OPENAI_CRED",
  "url": "https://api.openai.example.com/embeddings",
  "model": "embed-model"
}
Vertex AI example:
{
  "provider": "vertexai",
  "credential_name": "VERTEXAI_CRED",
  "url": "https://googleapis.example.com/models/",
  "model": "embed-model"
}

Table 205-12 Additional REST Provider Parameter Details

Parameter Description

input_type

Type of input to vectorize.

wait_for_model

Whether to wait for the model when it is not ready, as TRUE or FALSE.

For more information on additional parameters, refer to your third-party provider's documentation.

Note:

The generated embedding results may be different between requests for the same input and configuration, depending on your embedding model or floating point precision. However, this does not affect your queries (and provides semantically correct results) because the vector distance will be similar.

Examples

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

  • The following examples use UTL_TO_EMBEDDING to generate a vector embedding with Hello world as the input, by accessing the Generative AI embedding model:
    -- select example

var params clob;
exec :params := '
{ 
  "provider": "ocigenai",
  "credential_name": "OCI_CRED", 
  "url": "https://generativeai.oci.example.com/embedText",
  "model": "embed.modelname",
  "batch_size": 10
}';

select dbms_vector.utl_to_embedding('Hello world', json(:params)) from dual;

-- PL/SQL example

declare
  input clob;
  params clob;
  v vector;
begin
  input := 'Hello world';

  params := '
{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://generativeai.oci.example.com/embedText",
  "model": "embed.modelname"
}';

  v := dbms_vector.utl_to_embedding(input, json(params));
  dbms_output.put_line(vector_serialize(v));
exception
  when OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    DBMS_OUTPUT.PUT_LINE (SQLCODE);
end;
/

    To run an end-to-end example scenario using UTL_TO_EMBEDDING, see Directly Convert Text String to Embedding in Oracle Database AI Vector Search User's Guide.

  • The following example uses UTL_TO_EMBEDDINGS to generate vector embeddings with a PDF document (stored in the documentation_tab table) as the input, by calling pretrained ONNX model:
    SELECT et.* from documentation_tab dt,dbms_vector.utl_to_embeddings(dbms_vector.utl_to_chunks(dbms_vector.utl_to_text(dt.data)),
json(:embed_params)) et;

    To run an end-to-end example scenario using UTL_TO_EMBEDDINGS, see Step By Step Convert File to Text to Chunks to Embeddings in Oracle Database AI Vector Search User's Guide.

UTL_TO_GENERATE_TEXT

Use the DBMS_VECTOR.UTL_TO_GENERATE_TEXT chainable utility function to generate text for a given prompt.

Purpose

To generate text using a prompt, by accessing third-party text generation models.

This API makes a REST call to your specified third-party service provider, such as Cohere, Google AI, Hugging Face, Oracle Cloud Infrastructure (OCI) Generative AI, OpenAI, or Vertex AI.

A prompt can be an input string, such as a question that you ask a Large Language Model (LLM). For example, "What is Oracle Text?". A prompt can also be a command, such as "Summarize the following ...", "Draft an email asking for ...", or "Rewrite the following ...", and can include results from a search.

Syntax

DBMS_VECTOR.UTL_TO_GENERATE_TEXT (
    	DATA          IN CLOB,
    	PARAMS        IN JSON default NULL
) return CLOB;

DATA

This function accepts the input data type as CLOB, and returns the output as CLOB.

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>,
  "<additional REST provider parameter>": "<REST provider parameter value>"
}

Table 205-13 UTL_TO_GENERATE_TEXT Parameter Details

Parameter Description

provider

Service provider that you want to access to generate the text:

  • Cohere

  • GoogleAI

  • HuggingFace

  • OCIGenAI

  • OpenAI

  • 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.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 API endpoint for each REST call.

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.

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.

Additional REST provider parameters:

Cohere example:
{
  "provider": "Cohere", 
  "credential_name": "COHERE_CRED",
  "url": "https://api.cohere.example.com/generateText",
  "model": "generate-text-model"
}
Google AI example:
{
  "provider": "googleai",
  "credential_name": "GOOGLEAI_CRED",
  "url": "https://googleapis.example.com/models/",
  "model": "generate-text-model"
}
Hugging Face example:
{
  "provider": "huggingface",
  "credential_name": "HF_CRED",
  "url": "https://api.huggingface.example.com/models/",
  "model": "generate-text-model",
  "wait_for_model": "true"
}
Generative AI example:
{
  "provider": "OCIGenAI", 
  "credential_name": "GENAI_CRED",
  "url": "https://generativeai.oci.example.com/generateText",
  "model": "generate-text-model",
  "inferenceRequest": {
    "maxTokens": 300,
    "temperature": 1
  }
}
OpenAI example:
{
  "provider": "openai",
  "credential_name": "OPENAI_CRED",
  "url": "https://api.openai.example.com",
  "model": "generate-text-model",
  "max_tokens": 60,
  "temperature": 1.0
}
Vertex AI example:
{
  "provider": "vertexai",
  "credential_name":"VERTEXAI_CRED",
  "url": "https://googleapis.example.com/models/",
  "model": "generate-text-model",
  "generation_config": {
    "temperature": 0.9,
    "topP": 1,
    "candidateCount": 1,
    "maxOutputTokens": 256
  }
}

Table 205-14 Additional REST Provider Parameter Details

Parameter Description

wait_for_model

Whether to wait for the model when it is not ready, as TRUE or FALSE.

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.

For more information on additional parameters, refer to your third-party provider's documentation.

Examples

These statements generate text using "What is Oracle Text?" as the prompt, by making a REST call to Generative AI. 

-- select example

var params clob;
exec :params := '
{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://generativeai.oci.example.com/generateText",
  "model": "generate.modelname"
}';

select dbms_vector.utl_to_generate_text(
 'What is Oracle Text?',
 json(:params)) from dual;

-- PL/SQL example

declare
  input clob;
  params clob;
  output clob;
begin
  input := 'What is Oracle Text?';

  params := '
{
  "provider": "ocigenai",
  "credential_name": "OCI_CRED",
  "url": "https://generativeai.oci.example.com/generateText",
  "model": "generate.modelname"
}';

  output := dbms_vector.utl_to_generate_text(input, json(params));
  dbms_output.put_line(output);
  if output is not null then
    dbms_lob.freetemporary(output);
  end if;
exception
  when OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    DBMS_OUTPUT.PUT_LINE (SQLCODE);
end;
/

To run an end-to-end example scenario using this function, see Generate Text for a Prompt: PL/SQL Example in Oracle Database AI Vector Search User's Guide.