210 DBMS_VECTOR
The DBMS_VECTOR
package provides APIs to support common operations with Oracle AI Vector Search.
These functions accept their respective input parameters in JSON format.
Related Topics
210.1 Summary of DBMS_VECTOR Subprograms
The DBMS_VECTOR
package simplifies common operations with Oracle AI Vector Search, such as extracting chunks or embeddings from user data, generating text for a given prompt, and reporting on index accuracy.
This table lists the DBMS_VECTOR
subprograms and briefly describes them.
Table 210-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. |
|
Loads an ONNX model into the database |
|
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. |
|
Splits data into smaller pieces or chunks |
|
Converts data to one or more vector embeddings |
|
Generates text for a prompt (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. |
|
Creates a credential name |
|
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 |
Rebuilds a vector index |
|
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. |
|
Verifies the accuracy of a vector index |
|
INDEX_ACCURACY_REPORT | Captures accuracy values achieved by approximate searches |
Note:
DBMS_VECTOR
is a lightweight package that does not support text processing or summarization operations. Therefore, the UTL_TO_TEXT
and UTL_TO_SUMMARY
chainable utility functions and all the chunker helper procedures are available only in the advanced DBMS_VECTOR_CHAIN
package.
210.1.1 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 that if you are using Oracle 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.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.
{
"user_ocid": "<user ocid>",
"tenancy_ocid": "<tenancy ocid>",
"compartment_ocid": "<compartment ocid>",
"private_key": "<private key>",
"fingerprint": "<fingerprint>"
}
{ "access_token": "<access token>" }
Table 210-2 Parameter Details
Parameter | Description |
---|---|
|
Oracle Cloud Identifier (OCID) of the user, as listed on the User Details page in the OCI console. |
|
OCID of your tenancy, as listed on the Tenancy Details page in the OCI console. |
|
OCID of your compartment, as listed on the Compartments information page in the OCI console. |
|
OCI private key. Note: The generated private key may appear as:
You pass the <private key string> value (excluding the BEGIN and END lines), either as a single line or as multiple lines.
|
|
Fingerprint of the OCI profile key, as listed on the User Details page under API Keys in the OCI console. |
|
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_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 Oracle Database AI Vector Search User's Guide.
210.1.2 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 DEFAULT NULL,
IDX_PARTITIONING_SCHEME IN VARCHAR2 default 'LOCAL',
IDX_ORGANIZATION IN VARCHAR2,
IDX_DISTANCE_METRIC IN VARCHAR2 DEFAULT COSINE,
IDX_ACCURACY IN NUMBER DEFAULT 90,
IDX_PARAMETERS IN CLOB,
IDX_PARALLEL_CREATION IN NUMBER DEFAULT 1,
IDX_DDL IN CLOB DEFAULT NULL
);
Parameters
Specify the input parameters in JSON format.
Table 210-3 CREATE_INDEX (IN) Parameters of DBMS_VECTOR
Parameter | Description |
---|---|
|
Table on which to create the index. |
|
Name of the index. |
|
Vector column on which to create the index. |
|
Partitioning scheme. |
|
Index organization. Either |
|
Distance computation metric. Defaults to |
|
Target accuracy. |
|
Takes in CLOB with type of index and associated parameters. Type, neighbor partitions for IVF, neighbors/efConstruction for HNSW) . |
|
Number of parallel threads used for index construction. |
|
Defaults to |
210.1.3 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');
210.1.4 DROP_ONNX_MODEL Procedure
This procedure deletes the specified ONNX model.
Syntax
DBMS_VECTOR.DROP_ONNX_MODEL (model_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Parameters
Table 210-4 DROP_ONNX_MODEL Procedure Parameters
Parameter | Description |
---|---|
|
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. |
|
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 an ONNX model, you must be the owner or you must have the
DB_DEVELOPER_ROLE
.
Example
You can use the following command to delete a valid
ONNX model named doc_model
that exists in your
schema.
BEGIN DBMS_VECTOR.DROP_ONNX_MODEL(model_name => 'doc_model'); END; /
210.1.5 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 210-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
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.
210.1.6 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 210-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 |
end_time |
Specifies an end point up until which query vectors are
considered for accuracy computation. A |
For information about determining the accuracy of your vector indexes, see Index Accuracy Report in Oracle Database AI Vector Search User's Guide.
210.1.7 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 210-7 LOAD_ONNX_MODEL Procedure Parameters
Parameter | Description |
---|---|
|
The directory name of the data dump. For example,
|
|
A |
|
Name of the model in the form
|
|
It is a |
|
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 tasks210.1.7.1 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:
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 |
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
If you do not specify labels or
|
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
|
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 specifyinput
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 theinput
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
andclusteringProbOutput
. SpecifynormalizeProb
to define the normalization that must be applied for softmax normalization. The default setting isnone
, indicating that no normalization is applied. You can choosesoftmax
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 tasks210.1.8 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 'COSINE',
USE_INDEX IN BOOLEAN DEFAULT TRUE,
ACCURACY IN NUMBER DEFAULT '90',
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 DEFAULT NULL,
IDX_NAME IN VARCHAR2 DEFAULT NULL,
DISTANCE_METRIC IN VARCHAR2 DEFAULT 'COSINE',
USE_INDEX IN BOOLEAN DEFAULT TRUE,
ACCURACY IN NUMBER DEFAULT '90',
IDX_PARAMETERS IN CLOB DEFAULT NULL
) return JSON_ARRAY_T;
Parameters
Specify the input parameters in JSON format.
Table 210-8 DBMS_VECTOR.QUERY Parameters
Parameter | Description |
---|---|
|
Table name to query |
|
Vector column name |
|
Query vector passed in as |
|
Number of results to be returned. |
|
Columns to be projected as part of the result. |
|
Name of the index queried. |
|
Distance computation metric. Defaults to |
|
Specifies whether the search is an approximate search or exact search. Defaults to TRUE (that is, approximate). |
|
Specifies the minimum desired query accuracy. |
|
Specifies values of |
DATA
This function accepts the input data type as VARCHAR2
,
NUMBER
, JSON
, BOOLEAN
or
CLOB
.
210.1.9 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 'COSINE',
IDX_ACCURACY IN NUMBER DEFAULT 90,
IDX_PARAMETERS IN CLOB DEFAULT NULL,
IDX_PARALLEL_CREATION IN NUMBER DEFAULT 1,
);
Parameters
Specify the input parameters in JSON format.
Table 210-9 REBUILD_INDEX (IN) Parameters of DBMS_VECTOR
Parameter | Description |
---|---|
|
Table on which to create the index. |
|
Name of the index. |
|
Vector column on which to rebuild the index. |
|
Partitioning scheme. |
|
Index organization. Either |
|
Distance computation metric. Defaults to |
|
Target accuracy. |
|
Takes in CLOB with type of index and associated parameters. Type, neighbor partitions for IVF, neighbors/efConstruction for HNSW) . |
|
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.
210.1.10 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 CLOB
s, 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)"
}
{"chunk_id":1,"chunk_offset":1,"chunk_length":6,"chunk_data":"sample"}
-
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 of1
. -
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
, andextended
specify the same values as that ofVECTOR_CHUNKS
. -
vocabulary
is the name of the custom vocabulary; same as the vocabulary name that you specify when using theby vocabulary
mode. -
custom_list
is an array of the custom split characters; same as thesplit by custom
condition. -
norm_options
is an array of normalization options; same as thenormalize
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 Oracle Database AI Vector Search User's Guide.
210.1.11 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
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.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"
}
-
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.
{
"provider": "database",
"model": "<pretrained ONNX embedding model file name>"
}
Table 210-10 Database Provider Parameter Details
Parameter | Description |
---|---|
|
Specify |
|
User-specified name under which the imported pretrained ONNX embedding model is stored in Oracle Database. If you do not have a 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 210-11 Third-Party Provider Parameter Details
Parameter | Description |
---|---|
|
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:
|
|
Name of the credential in the form:
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 |
|
URL of the API endpoint for each REST call. |
|
Name of the third-party embedding model in the form:
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. |
|
Maximum time to wait for the request to complete. The default value is |
|
Maximum number of vectors to request at a time. For example, for a batch size of 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:
{
"provider": "cohere",
"credential_name": "COHERE_CRED",
"url": "https://api.cohere.example.com/embed",
"model": "embed-model",
"input_type": "search_query"
}
{
"provider": "googleai",
"credential_name": "GOOGLEAI_CRED",
"url": "https://googleapis.example.com/models/",
"model": "embed-model"
}
{
"provider": "huggingface",
"credential_name": "HF_CRED",
"url": "https://api.huggingface.example.com/",
"model": "embed-model"
}
{
"provider": "ocigenai",
"credential_name": "OCI_CRED",
"url": "https://generativeai.oci.example.com/embedText",
"model": "embed-model",
"batch_size": 10
}
{
"provider": "openai",
"credential_name": "OPENAI_CRED",
"url": "https://api.openai.example.com/embeddings",
"model": "embed-model"
}
{
"provider": "vertexai",
"credential_name": "VERTEXAI_CRED",
"url": "https://googleapis.example.com/models/",
"model": "embed-model"
}
Table 210-12 Additional REST Provider Parameter Details
Parameter | Description |
---|---|
|
Type of input to vectorize. |
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 withHello 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 Oracle Database AI Vector Search User's Guide. -
The following example uses
UTL_TO_EMBEDDINGS
to generate vector embeddings from an extract on 'transactions' (stored in thedocs
table), by accessing the Generative AI embedding model:drop table docs; create table docs(id number primary key, text clob); insert into docs(id, text) values(1, 'A transaction is a logical, atomic unit of work that contains one or more SQL statements. An RDBMS must be able to group SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone. An illustration of the need for transactions is a funds transfer from a savings account to a checking account.'); var chunk_params clob; var embed_params clob; begin :chunk_params := '{ "by": "words", "max": 50 }'; :embed_params := '{ "provider": "ocigenai", "credential_name": "OCI_CRED", "url": "https://generativeai.oci.example.com/embedText", "model": "embed.modelname" }'; end; / SELECT et.* from docs dt, dbms_vector.utl_to_embeddings(dbms_vector.utl_to_chunks(dt.text, json(:chunk_params)), json(:embed_params)) et;
Related Topics
210.1.12 UTL_TO_GENERATE_TEXT
Use the DBMS_VECTOR.UTL_TO_GENERATE_TEXT
chainable utility function to generate text for a given prompt, by accessing third-party text generation models.
Purpose
To communicate with Large Language Models (LLMs) for generating a textual description for prompts, given as input to LLM-powered chat interfaces.
A prompt can be an input text string, such as a question that you ask an LLM. For example, "What is Oracle Text?
". A prompt can also be a set of instructions or a command, such as "Summarize the following ...
", "Draft an email asking for ...
", or "Rewrite the following ...
", and can include results from a search. This API responds with a textual answer, description, or summary based on the specified task in the prompt.
For this operation, this API makes a REST call to a third-party service provider, such as Cohere, Google AI, Hugging Face, Oracle Cloud Infrastructure (OCI) Generative AI, OpenAI, or Vertex AI.
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. It then processes this information to generate a new CLOB
containing the generated text.
DBMS_VECTOR.UTL_TO_GENERATE_TEXT (
DATA IN CLOB,
PARAMS IN JSON default NULL
) return CLOB;
DATA
Specify the textual prompt as CLOB
for the DATA
clause.
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 210-13 UTL_TO_GENERATE_TEXT Parameter Details
Parameter | Description |
---|---|
|
Supported REST provider that you want to access to generate text. Specify one of the following values:
|
|
Name of the credential in the form:
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 |
|
URL of the API endpoint for each REST call. |
|
Name of the third-party text generation model in the form:
If the model name is not schema-qualified, then the schema of the procedure invoker is used. |
|
Maximum time to wait for the request to complete. The default value is |
Additional REST provider parameters:
{
"provider": "Cohere",
"credential_name": "COHERE_CRED",
"url": "https://api.cohere.example.com/generateText",
"model": "generate-text-model"
}
{
"provider": "googleai",
"credential_name": "GOOGLEAI_CRED",
"url": "https://googleapis.example.com/models/",
"model": "generate-text-model"
}
{
"provider": "huggingface",
"credential_name": "HF_CRED",
"url": "https://api.huggingface.example.com/models/",
"model": "generate-text-model"
}
{
"provider": "OCIGenAI",
"credential_name": "GENAI_CRED",
"url": "https://generativeai.oci.example.com/generateText",
"model": "generate-text-model",
"inferenceRequest": {
"maxTokens": 300,
"temperature": 1
}
}
{
"provider": "openai",
"credential_name": "OPENAI_CRED",
"url": "https://api.openai.example.com",
"model": "generate-text-model",
"max_tokens": 60,
"temperature": 1.0
}
{
"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 210-14 Additional REST Provider Parameter Details
Parameter | Description |
---|---|
|
Maximum number of tokens in the output text. |
|
Degree of randomness used when generating the output text, in the range of To generate the same output for a prompt, use Note: Start with the temperature set to |
|
Probability of tokens in the output, in the range of A lower value provides less random responses and a higher value provides more random responses. |
|
Number of response variations to return, in the range of |
|
Maximum number of tokens to generate for each response. |
For more information on additional parameters, refer to your third-party provider's documentation.
Example
The following statements generate text by making a REST call to Generative AI. Here, the prompt is "What is Oracle Text?
".
-- 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, see Generate Text Using Third-Party APIs in Oracle Database AI Vector Search User's Guide.