212 DBMS_VECTOR_CHAIN
The DBMS_VECTOR_CHAIN
package provides APIs to support advanced operations with Oracle AI Vector Search, such as chunking and embedding data along with text generation and summarization capabilities. It is more suitable for text processing with similarity search and hybrid search, using functionality that can be pipelined together for an end-to-end search.
These functions accept their respective input parameters in JSON format.
Related Topics
212.1 Summary of DBMS_VECTOR_CHAIN Subprograms
This table lists the DBMS_VECTOR_CHAIN
subprograms and
briefly describes them.
Table 212-1 DBMS_VECTOR_CHAIN Package Subprograms
Subprogram | Description |
---|---|
Chainable Utility (UTL) Functions: These functions are a set of modular and flexible functions within vector utility PL/SQL packages. You can chain these together to automate end-to-end data transformation and similarity search operations. |
|
Extracts plain text data from documents |
|
Splits data into smaller pieces or chunks |
|
Converts data to one or more vector embeddings |
|
Extracts a summary from documents |
|
Generates text for a prompt (input string) or an image |
|
Credential Helper Procedures: These procedures enable you to securely manage authentication credentials in the database. You require these credentials to enable access to third-party service providers for making REST calls. |
|
Creates a credential name |
|
Drops an existing credential name |
|
Preference Helper Procedures: These procedures enable you to manage vectorizer preferences, to be used with the |
|
Creates a vectorizer preference |
|
Drops an existing vectorizer preference |
|
Chunker Helper Procedures: These procedures enable you to configure vocabulary and language data (abbreviations), to be used with the |
|
Loads your token vocabulary file into the database |
|
Removes existing vocabulary data |
|
Loads your language data file into the database |
|
Removes existing abbreviation data |
|
Data Access Function: This function enables you to enhance search operations. |
|
Reorders search results for more relevant output |
Note:
The DBMS_VECTOR_CHAIN
package requires you to install the CONTEXT
component of Oracle Text, an Oracle Database technology that provides indexing, term extraction, text analysis, text summarization, word and theme searching, and other utilities.
Due to underlying dependance on the text processing capabilities of Oracle Text, note that both the UTL_TO_TEXT
and UTL_TO_SUMMARY
chainable utility functions and all the chunker helper procedures are available only in this package through Oracle Text.
212.1.1 CREATE_CREDENTIAL
Use the DBMS_VECTOR_CHAIN.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_CHAIN.CREATE_CREDENTIAL (
CREDENTIAL_NAME IN VARCHAR2,
PARAMS IN JSON DEFAULT NULL
);
CREDENTIAL_NAME
Specify a name of the credential that you want to create for holding authentication parameters.
PARAMS
Specify authentication parameters in JSON format, based on your chosen service provider.
{
"user_ocid" : "<user ocid>",
"tenancy_ocid" : "<tenancy ocid>",
"compartment_ocid": "<compartment ocid>",
"private_key" : "<private key>",
"fingerprint" : "<fingerprint>"
}
{ "access_token": "<access token>" }
Table 212-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_chain.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_chain.create_credential( credential_name => 'COHERE_CRED', params => json(jo.to_string)); end; /
End-to-end examples:
To run end-to-end example scenarios using this procedure, see Use LLM-Powered APIs to Generate Summary and Text.
212.1.2 CREATE_LANG_DATA
Use the DBMS_VECTOR_CHAIN.CREATE_LANG_DATA
chunker helper procedure to load your own language data file into the database.
Purpose
To create custom language data for your chosen language (specified using the language
chunking parameter).
A language data file contains language-specific abbreviation tokens. You can supply this data to the chunker to help in accurately determining sentence boundaries of chunks, by using knowledge of the input language's end-of-sentence (EOS) punctuations, abbreviations, and contextual rules.
Usage Notes
-
All supported languages are distributed with the default language-specific abbreviation dictionaries. You can create a language data based on the abbreviation tokens loaded in the
schema.table.column
, using a user-specified language data name (PREFERENCE_NAME
). -
After loading your language data, you can use language-specific chunking by specifying the
language
chunking parameter withVECTOR_CHUNKS
orUTL_TO_CHUNKS
. -
You can query these data dictionary views to access existing language data:
-
ALL_VECTOR_LANG
displays all available languages data. -
USER_VECTOR_LANG
displays languages data from the schema of the current user. -
ALL_VECTOR_ABBREV_TOKENS
displays abbreviation tokens from all available language data. -
USER_VECTOR_ABBREV_TOKENS
displays abbreviation tokens from the language data owned by the current user.
-
Syntax
DBMS_VECTOR_CHAIN.CREATE_LANG_DATA (
PARAMS IN JSON default NULL
);
PARAMS
{
table_name,
column_name,
language,
preference_name
}
Table 212-3 Parameter Details
Parameter | Description | Required | Default Value |
---|---|---|---|
|
Name of the table (along with the optional table owner) in which you want to load the language data |
Yes |
No value |
|
Column name in the language data table in which you want to load the language data |
Yes |
No value |
|
Any supported language name, as listed in Supported Languages and Data File Locations. |
Yes |
No value |
|
User-specified preference name for this language data |
Yes |
No value |
Example
declare
params CLOB := '{"table_name" : "eos_data_1",
"column_name" : "token",
"language" : "indonesian",
"preference_name" : "my_lang_1"}';
begin
DBMS_VECTOR_CHAIN.CREATE_LANG_DATA(
JSON (params));
end;
/
End-to-end example:
To run an end-to-end example scenario using this procedure, see Create and Use Custom Language Data.
Related Topics
212.1.3 CREATE_PREFERENCE
Use the DBMS_VECTOR_CHAIN.CREATE_PREFERENCE
preference helper procedure to create a vectorizer preference, to be used when creating or updating hybrid vector indexes.
Purpose
To create a vectorizer preference. This allows you to customize vector search parameters of a hybrid vector indexing pipeline. The goal of a vectorizer preference is to provide you with a straightforward way to configure how to chunk or embed your documents, without requiring a deep understanding of various chunking or embedding strategies.
A vectorizer preference is a JSON object that collectively holds user-specified values related to the following chunking, embedding, or vector index creation parameters:
-
Chunking (
UTL_TO_CHUNKS
orVECTOR_CHUNKS
) -
Embedding (
UTL_TO_EMBEDDING
,UTL_TO_EMBEDDINGS
, orVECTOR_EMBEDDING
) -
Vector index creation (
distance
,accuracy
, andvector_idxtype
)
After creating a vectorizer preference, you can use the VECTORIZER
parameter to pass this preference name in the paramstring
of the PARAMETERS
clause for CREATE_HYBRID_VECTOR_INDEX
and ALTER_INDEX
SQL statements.
Usage Notes
-
Creating a preference is optional. If you do not specify any optional preference, then the index is created with system defaults.
-
All vector index preferences follow the same JSON syntax as defined for their corresponding
DBMS_VECTOR
andDBMS_VECTOR_CHAIN
APIs.
Syntax
DBMS_VECTOR_CHAIN.CREATE_PREFERENCE (
PREF_NAME IN VARCHAR2,
PREF_TYPE IN VARCHAR2,
PARAMS IN JSON default NULL
);
PREF_NAME
Specify the name of the vectorizer preference to create.
PREF_TYPE
Type of preference. The only supported preference type is:
DBMS_VECTOR_CHAIN.VECTORIZER
PARAMS
Specify vector search-specific parameters in JSON format.
-
Embedding Parameter:
{ "model" : <embedding_model_for_vector_generation> }
For example:
{ "model" : MY_INDB_MODEL }
model
specifies the name under which your ONNX embedding model is stored in the database.If you do not have an in-database embedding model in ONNX format, then perform the steps listed in Oracle Database AI Vector Search User's Guide.
-
Chunking Parameters:
{ "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" ] }')
Parameter Description and Acceptable Values by
Specify a mode for splitting your data, that is, to split by counting the number of characters, words, or vocabulary tokens.
Valid values:
-
characters
(orchars
):Splits by counting the number of characters.
-
words
:Splits by counting the number of words.
Words are defined as sequences of alphabetic characters, sequences of digits, individual punctuation marks, or symbols. For segmented languages without whitespace word boundaries (such as Chinese, Japanese, or Thai), each native character is considered a word (that is, unigram).
-
vocabulary
:Splits by counting the number of vocabulary tokens.
Vocabulary tokens are words or word pieces, recognized by the vocabulary of the tokenizer that your embedding model uses. You can load your vocabulary file using the chunker helper API
DBMS_VECTOR_CHAIN.CREATE_VOCABULARY
.Note: For accurate results, ensure that the chosen model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model.
Default value:
words
max
Specify a limit on the maximum size of each chunk. This setting splits the input text at a fixed point where the maximum limit occurs in the larger text. The units of
max
correspond to theby
mode, that is, to split data when it reaches the maximum size limit of a certain number of characters, words, numbers, punctuation marks, or vocabulary tokens.Valid values:
-
by characters
:50
to4000
characters -
by words
:10
to1000
words -
by vocabulary
:10
to1000
tokens
Default value:
100
split [by]
Specify where to split the input text when it reaches the maximum size limit. This helps to keep related data together by defining appropriate boundaries for chunks.
Valid values:
-
none
:Splits at the
max
limit of characters, words, or vocabulary tokens. -
newline
,blankline
, andspace
:These are single-split character conditions that split at the last split character before the
max
value.Use
newline
to split at the end of a line of text. Useblankline
to split at the end of a blank line (sequence of characters, such as two newlines). Usespace
to split at the end of a blank space. -
recursively
:This is a multiple-split character condition that breaks the input text using an ordered list of characters (or sequences).
recursively
is predefined asBLANKLINE
,newline
,space
,none
in this order:1. If the input text is more than the
max
value, then split by the first split character.2. If that fails, then split by the second split character.
3. And so on.
4. If no split characters exist, then split by
max
wherever it appears in the text. -
sentence
:This is an end-of-sentence split condition that breaks the input text at a sentence boundary.
This condition automatically determines sentence boundaries by using knowledge of the input language's sentence punctuation and contextual rules. This language-specific condition relies mostly on end-of-sentence (EOS) punctuations and common abbreviations.
Contextual rules are based on word information, so this condition is only valid when splitting the text by words or vocabulary (not by characters).
Note: This condition obeys the
by word
andmax
settings, and thus may not determine accurate sentence boundaries in some cases. For example, when a sentence is larger than themax
value, it splits the sentence atmax
. Similarly, it includes multiple sentences in the text only when they fit within themax
limit. -
custom
:Splits based on a custom split characters list. You can provide custom sequences up to a limit of
16
split character strings, with a maximum length of10
each.Specify an array of valid text literals using the
custom_list
parameter.{ "split" : "custom", "custom_list" : [ "split_chars1", ... ] }
For example:
{ "split" : "custom", "custom_list" : [ "<p>" , "<s>" ] }
Note: You can omit sequences only for tab (
\t
), newline (\n
), and linefeed (\r
).
Default value:
recursively
overlap
Specify the amount (as a positive integer literal or zero) of the preceding text that the chunk should contain, if any. This helps in logically splitting up related text (such as a sentence) by including some amount of the preceding chunk text.
The amount of overlap depends on how the maximum size of the chunk is measured (in characters, words, or vocabulary tokens). The overlap begins at the specified
split
condition (for example, atnewline
).Valid value:
5%
to20%
ofmax
Default value:
0
language
Specify the language of your input data.
This clause is important, especially when your text contains certain characters (for example, punctuations or abbreviations) that may be interpreted differently in another language.
Valid values:
-
NLS-supported language name or its abbreviation, as listed in Oracle Database Globalization Support Guide.
-
Custom language name or its abbreviation, as listed in Supported Languages and Data File Locations. You use the
DBMS_VECTOR_CHAIN.CREATE_LANG_DATA
chunker helper API to load language-specific data (abbreviation tokens) into the database, for your specified language.
Default value:
NLS_LANGUAGE
from sessionnormalize
Automatically pre-processes or post-processes issues (such as multiple consecutive spaces and smart quotes) that may arise when documents are converted into text. Oracle recommends you to use a normalization mode to extract high-quality chunks.
Valid values:
-
none
:Applies no normalization.
-
all
:Normalizes common multi-byte (unicode) punctuation to standard single-byte.
-
options
:Specify an array of normalization options using the
norm_options
parameter.{ "normalize" : "options", "norm_options" : [ "normalize_option1", ... ] }
-
punctuation
:Includes smart quotes, smart hyphens, and other multi-byte equivalents to simple single-byte punctuation.
For example:-
2018u 'map to 0027'
-
2019u 'map to 0027'
-
201Bu 'map to 0027'
-
-
whitespace
:Minimizes whitespace by eliminating unnecessary characters.
For example, retain blanklines, but remove any extra newlines and interspersed spaces or tabs:
" \n \n " => "\n\n"
-
widechar
:Normalizes wide, multi-byte digits and (a-z) letters to single-byte.
These are multi-byte equivalents for
0-9
anda-z A-Z
, which can show up inZH/JA
formatted text.
For example:
{ "normalize" : "options", "norm_options" : [ "whitespace" ] }
-
Default value: None
extended
Increases the output limit of a
VARCHAR2
string to32767
bytes, without requiring you to set themax_string_size
parameter toextended
.Default value:
4000
or32767
(whenmax_string_size=extended
) -
-
Vector Index Parameters:
{ "distance" : <vector_distance>, "accuracy" : <vector_accuracy>, "vector_idxtype" : <vector_idxtype> }
For example:
{ "distance" : COSINE, "accuracy" : 95, "vector_idxtype" : HNSW }
Parameter Description distance
Distance computation metric as
COSINE
,MANHATTAN
,DOT
,EUCLIDEAN
,L2_SQUARED
, orEUCLIDEAN_SQUARED
.Note: Currently, the
HAMMING
andJACCARD
vector distance metrics are not supported with hybrid vector indexes.For detailed information on each of these metrics, see Vector Distance Functions and Operators.
Default value:
COSINE
accuracy
Target accuracy at which the approximate search should be performed when running an approximate search query using vector indexes. You can specify non-default target accuracy values either by specifying a percentage value or by specifying internal parameters values, depending on the index type you are using.
-
For an IVF approximate search: Specify a target accuracy percentage value to influence the number of partitions used to probe the search. Instead of specifying a target accuracy percentage value, you can specify the
NEIGHBOR PARTITION PROBES
parameter to impose a certain maximum number of partitions to be probed by the search. See Understand Inverted File Flat Vector Indexes. -
For an HNSW approximate search: Specify a target accuracy percentage value to influence the number of candidates considered to probe the search. Instead of specifying a target accuracy percentage value, you can specify the
EFSEARCH
parameter to impose a certain maximum number of candidates to be considered while probing the index. See Understand Hierarchical Navigable Small World Indexes.
Valid range for both IVF and HNSW vector indexes is:
ACCURACY: > 0 and <= 100
Default value: None
vector_idxtype
Type of vector index to create:
-
IVF
for the Inverted File Flat (IVF) vector index -
HNSW
for the Hierarchical Navigable Small World (HNSW) vector index
For detailed information on each of these indexes, see Manage the Different Categories of Vector Indexes.
Default value:
IVF
-
Example
begin
DBMS_VECTOR_CHAIN.CREATE_PREFERENCE(
'my_vec_spec',
DBMS_VECTOR_CHAIN.VECTORIZER,
json('{"vector_idxtype" : "hnsw",
"model" : "my_doc_model",
"by" : "words",
"max" : 100,
"overlap" : 10,
"split" : "recursively"}'));
end;
/
CREATE HYBRID VECTOR INDEX my_hybrid_idx on
doc_table(text_column)
parameters('VECTORIZER my_vec_spec');
Related Topics
212.1.4 CREATE_VOCABULARY
Use the DBMS_VECTOR_CHAIN.CREATE_VOCABULARY
chunker helper procedure to load your own token vocabulary file into the database.
Purpose
To create custom token vocabulary that is recognized by the tokenizer used by your vector embedding model.
A vocabulary contains a set of tokens (words and word pieces) that are collected during a model's statistical training process. You can supply this data to the chunker to help in accurately selecting the text size that approximates the maximum input limit imposed by the tokenizer of your embedding model.
Usage Notes
-
Usually, the supported vocabulary files (containing recognized tokens) are included as part of a model's distribution. Oracle recommends to use the vocabulary files associated with your model.
If a vocabulary file is not available, then you may download one of the following files depending on the tokenizer type:-
WordPiece:
Vocabulary file (
vocab.txt
) for the "bert-base-uncased" (English) or "bert-base-multilingual-cased" model -
Byte-Pair Encoding (BPE):
Vocabulary file (
vocab.json
) for the "GPT2" modelUse the following python script to extract the file:import json import sys with open(sys.argv[1], encoding="utf-8") as f: d = json.load(f) for term in d: print(term)
-
SentencePiece:
Vocabulary file (
tokenizer.json
) for the "xlm-roberta-base" modelUse the following python script to extract the file:import json import sys with open(sys.argv[1], encoding="utf-8") as f: d = json.load(f) for entry in d["model"]["vocab"]: print(entry[0])
Ensure to save your vocabulary files in
UTF-8
encoding. -
-
You can create a vocabulary based on the tokens loaded in the
schema.table.column
, using a user-specified vocabulary name (vocabulary_name
).After loading your vocabulary data, you can use the
by vocabulary
chunking mode (withVECTOR_CHUNKS
orUTL_TO_CHUNKS
) to split input data by counting the number of tokens. -
You can query these data dictionary views to access existing vocabulary data:
-
ALL_VECTOR_VOCAB
displays all available vocabularies. -
USER_VECTOR_VOCAB
displays vocabularies from the schema of the current user. -
ALL_VECTOR_VOCAB_TOKENS
displays a list of tokens from all available vocabularies. -
USER_VECTOR_VOCAB_TOKENS
displays a list of tokens from the vocabularies owned by the current user.
-
Syntax
DBMS_VECTOR_CHAIN.CREATE_VOCABULARY(
PARAMS IN JSON default NULL
);
PARAMS
{
table_name,
column_name,
vocabulary_name,
format,
cased
}
Table 212-4 Parameter Details
Parameter | Description | Required | Default Value |
---|---|---|---|
|
Name of the table (along with the optional table owner) in which you want to load the vocabulary file |
Yes |
No value |
|
Column name in the vocabulary table in which you want to load the vocabulary file |
Yes |
No value |
|
User-specified name of the vocabulary, along with the optional owner name (if other than the current owner) |
Yes |
No value |
|
|
Yes |
No value |
|
Character-casing of the vocabulary, that is, vocabulary to be treated as cased or uncased |
No |
|
Example
DECLARE
params clob := '{"table_name" : "doc_vocabtab",
"column_name" : "token",
"vocabulary_name" : "doc_vocab",
"format" : "bert",
"cased" : false}';
BEGIN
dbms_vector_chain.create_vocabulary(json(params));
END;
/
End-to-end example:
To run an end-to-end example scenario using this procedure, see Create and Use Custom Vocabulary.
Related Topics
212.1.5 DROP_CREDENTIAL
Use the DBMS_VECTOR_CHAIN.DROP_CREDENTIAL
credential helper procedure to drop an existing credential name from the data dictionary.
Syntax
DBMS_VECTOR_CHAIN.DROP_CREDENTIAL (
CREDENTIAL_NAME IN VARCHAR2
);
CREDENTIAL_NAME
Specify the credential name that you want to drop.
Examples
-
For Generative AI:
exec dbms_vector_chain.drop_credential('OCI_CRED');
-
For Cohere:
exec dbms_vector_chain.drop_credential('COHERE_CRED');
212.1.6 DROP_LANG_DATA
Use the DBMS_VECTOR_CHAIN.DROP_LANG_DATA
chunker helper procedure to remove abbreviation data from the data dictionary.
Syntax
DBMS_VECTOR_CHAIN.DROP_LANG_DATA(
PREF_NAME IN VARCHAR2
);
LANG
Specify the name of the language data that you want to drop for a given language.
Example
DBMS_VECTOR_CHAIN.DROP_LANG_DATA('indonesian');
212.1.7 DROP_PREFERENCE
Use the DBMS_VECTOR_CHAIN.DROP_PREFERENCE
preference helper procedure to remove an existing Vectorizer preference.
Syntax
DBMS_VECTOR_CHAIN.DROP_PREFERENCE (PREF_NAME);
PREF_NAME
Name of the Vectorizer preference to drop.
Example
DBMS_VECTOR_CHAIN.DROP_PREFERENCE ('scott_vectorizer');
212.1.8 DROP_VOCABULARY
Use the DBMS_VECTOR_CHAIN.DROP_VOCABULARY
chunker helper procedure to remove vocabulary data from the data dictionary.
Syntax
DBMS_VECTOR_CHAIN.DROP_VOCABULARY(
VOCABULARY_NAME IN VARCHAR2
);
VOCAB_NAME
Specify the name of the vocabulary that you want to drop, in the form:
vocabulary_name
or
owner.vocabulary_name
Example
DBMS_VECTOR_CHAIN.DROP_VOCABULARY('MY_VOCAB_1');
212.1.9 RERANK
Use the DBMS_VECTOR_CHAIN.RERANK
function to reassess and reorder an initial set of results to retrieve more relevant search output.
Purpose
To improve the relevance and quality of search results in both similarity search and Retrieval Augmented Generation (RAG) scenarios.
Reranking improves the quality of information ingested into an LLM by ensuring that the most relevant documents or chunks are prioritized. This helps to reduce hallucinations and improves the accuracy of generated outputs.
For this operation, Oracle AI Vector Search supports reranking models provided by Cohere and 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_CHAIN.RERANK(
QUERY IN CLOB,
DOCUMENTS IN JSON,
PARAMS IN JSON default NULL
) return JSON;
This function accepts the input containing a query as CLOB
and a list of documents in JSON
format. It then processes this information to generate a JSON
object containing a reranked list of documents, sorted by score.
{
"index" : "1",
"score" : "0.99",
"content" : "Jupiter boasts an impressive system of 95 known moons."
}
-
index
specifies the position of the document in the list of input text. -
score
specifies the relevance score. -
content
specifies the input text corresponding to the index.
QUERY
Specify the search query (typically from an initial search) as CLOB
.
DOCUMENTS
Specify a JSON array of strings (list of potentially relevant documents to rerank) in the following format:
{
"documents": [
"string1",
"string2",
...
]
}
PARAMS
Specify the following list of parameters in JSON format. All these parameters are mandatory.
{
"provider" : "<service provider>",
"credential_name" : "<credential name>",
"url" : "<REST endpoint URL for reranking>",
"model" : "<reranking model name>",
...
}
Table 212-5 RERANK Parameter Details
Parameter | Description |
---|---|
|
Supported REST provider to access for reranking:
|
|
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 See CREATE_CREDENTIAL. |
|
URL of the API endpoint for each REST call. Note: For a list of all supported REST endpoints, see Supported Third-Party Provider Operations and Endpoints. |
|
Name of the reranking model in the form:
If the model name is not schema-qualified, then the schema of the procedure invoker is used. |
Additional REST provider parameters:
Optionally, specify additional provider-specific parameters for reranking.
Important:
The following examples are for illustration purposes. For accurate and up-to-date information on additional parameters to use, refer to your third-party provider's documentation.{
"provider" : "cohere",
"credential_name" : "COHERE_CRED",
"url" : "https://api.cohere.example.com/rerank",
"model" : "rerank-model",
"return_documents": false,
"top_n" : 3
}
{
"provider" : "vertexai",
"credential_name" : "VERTEXAI_CRED",
"url" : "https://googleapis.example.com/default_ranking_config:rank",
"model" : "rerank-model",
"ignoreRecordDetailsInResponse" : true,
"topN" : 3
}
Table 212-6 Additional REST Provider Parameter Details
Parameter | Description |
---|---|
|
Whether to return search results with original documents or input text (
Note: With Cohere as the provider, Oracle recommends that you keep this option disabled for better performance. You may choose to enable it for debugging purposes when you need to view the original text. |
|
Whether to return search results with original record details or input text (
Note: With Vertex AI as the provider, Oracle recommends that you keep this option enabled for better performance. You may choose to disable it for debugging purposes when you need to view the original text. |
|
The number of most relevant documents to return. |
Examples
-
Using Cohere:
declare params clob; reranked_output json; begin params := ' { "provider": "cohere", "credential_name": "COHERE_CRED", "url": "https://api.cohere.com/v1/rerank", "model": "rerank-english-v3.0", "return_documents": true, "top_n": 3 }'; reranked_output := dbms_vector_chain.rerank(:query, json(:initial_retrieval_docs), json(params)); dbms_output.put_line(json_serialize(reranked_output)); end; /
-
Using Vertex AI:
declare params clob; reranked_output json; begin params := ' { "provider": "vertexai", "credential_name": "VERTEXAI_CRED", "url": "https://discoveryengine.googleapis.com/v1/projects/1085581009881/locations/global/rankingConfigs/default_ranking_config:rank", "model": "semantic-ranker-512@latest", "ignoreRecordDetailsInResponse": false, "topN": 3 }'; reranked_output := dbms_vector_chain.rerank(:query, json(:initial_retrieval_docs), json(params)); dbms_output.put_line(json_serialize(reranked_output)); end; /
End-to-end example:
To run an end-to-end example scenario using this function, see Use Reranking for Better RAG Results.
212.1.10 UTL_TO_CHUNKS
Use the DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS
chainable utility function to split a large plain text document into smaller chunks of text.
Purpose
To perform a text-to-chunks transformation. This chainable utility function internally calls the VECTOR_CHUNKS
SQL function for the operation.
To embed a large document, you may first need to split it into multiple appropriate-sized segments or chunks through a splitting process known as chunking (as explained in Understand the Stages of Data Transformations). A chunk can be words (to capture specific words or word pieces), sentences (to capture a specific meaning), or paragraphs (to capture broader themes). A single document may be split into multiple chunks, each transformed into a vector.
Syntax
DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS (
DATA IN CLOB | VARCHAR2
PARAMS IN JSON default NULL
) return VECTOR_ARRAY_T;
DATA
This function accepts the input data type as CLOB
or VARCHAR2
.
It returns an array of 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 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" ]
}')
Here is a complete description of these parameters:
Parameter | Description and Acceptable Values |
---|---|
|
Specify a mode for splitting your data, that is, to split by counting the number of characters, words, or vocabulary tokens. Valid values:
Default value: |
|
Specify a limit on the maximum size of each chunk. This setting splits the input text at a fixed point where the maximum limit occurs in the larger text. The units of Valid values:
Default value: |
|
Specify where to split the input text when it reaches the maximum size limit. This helps to keep related data together by defining appropriate boundaries for chunks. Valid values:
Default value: |
|
Specify the amount (as a positive integer literal or zero) of the preceding text that the chunk should contain, if any. This helps in logically splitting up related text (such as a sentence) by including some amount of the preceding chunk text. The amount of overlap depends on how the maximum size of the chunk is measured (in characters, words, or vocabulary tokens). The overlap begins at the specified Valid value: Default value: |
|
Specify the language of your input data. This clause is important, especially when your text contains certain characters (for example, punctuations or abbreviations) that may be interpreted differently in another language. Valid values:
Default value: |
|
Automatically pre-processes or post-processes issues (such as multiple consecutive spaces and smart quotes) that may arise when documents are converted into text. Oracle recommends you to use a normalization mode to extract high-quality chunks. Valid values:
Default value: None |
|
Increases the output limit of a Default value: |
Example
SELECT D.id doc,
JSON_VALUE(C.column_value, '$.chunk_id' RETURNING NUMBER) AS id,
JSON_VALUE(C.column_value, '$.chunk_offset' RETURNING NUMBER) AS pos,
JSON_VALUE(C.column_value, '$.chunk_length' RETURNING NUMBER) AS siz,
JSON_VALUE(C.column_value, '$.chunk_data') AS txt
FROM docs D,
dbms_vector_chain.utl_to_chunks(D.text,
JSON('{ "by" : "words",
"max" : "100",
"overlap" : "0",
"split" : "recursively",
"language" : "american",
"normalize": "all" }')) C;
End-to-end examples:
To run end-to-end example scenarios using this function, see Perform Chunking With Embedding and Configure Chunking Parameters.
Related Topics
212.1.11 UTL_TO_EMBEDDING and UTL_TO_EMBEDDINGS
Use the DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING
and DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS
chainable utility functions to generate one or more vector embeddings from textual documents and images.
Purpose
To automatically generate vector embeddings from a set of textual documents and images.
-
Text to Vector:
You can perform a text-to-embedding transformation by accessing:
-
Oracle Database as the service provider: Calls an ONNX format embedding model that you load into the database (default setting)
-
Third-party embedding model: Makes a REST API call to either a remote service provider (Cohere, Google AI, Hugging Face, Generative AI, OpenAI, or Vertex AI) or a local service provider (Ollama)
-
-
Image to Vector:
You can also perform an image-to-embedding transformation. For this operation, this API makes a REST call to your chosen image embedding model or multimodal embedding model by Vertex AI (which is the only supported third-party provider).
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
-
Text to Vector:
DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING ( DATA IN CLOB, PARAMS IN JSON default NULL ) return VECTOR;
DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS ( DATA IN VECTOR_ARRAY_T, PARAMS IN JSON default NULL ) return VECTOR_ARRAY_T;
-
Image to Vector:
DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING ( DATA IN BLOB, MODALITY IN VARCHAR2, PARAMS IN JSON default NULL ) return VECTOR;
DATA
UTL_TO_EMBEDDING
accepts the input as CLOB
containing textual data (text strings or small documents) or as BLOB
containing media data (for media files such as images). It then converts the text or image input to a single embedding (VECTOR
).
UTL_TO_EMBEDDINGS
converts an array of chunks (VECTOR_ARRAY_T
) to an array of embeddings (VECTOR_ARRAY_T
).
A generated 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.
MODALITY
For BLOB
inputs, specify the type of content to vectorize. The only supported value is image
.
PARAMS
Specify input parameters in JSON format, depending on the service provider that you want to use.
{
"provider" : "database",
"model" : "<in-database ONNX embedding model filename>"
}
Table 212-7 Database Provider Parameter Details
Parameter | Description |
---|---|
|
Specify |
|
User-specified name under which the imported ONNX embedding model is stored in Oracle Database. If you do not have an embedding model in ONNX format, then perform the steps listed in Convert Pretrained Models to ONNX Format. |
If using a third-party provider:
Set the following parameters along with additional embedding parameters specific to your provider:
-
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 212-8 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. For image input, specify For text input, 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. Note: For a list of all supported REST endpoints, see Supported Third-Party Provider Operations and Endpoints. |
|
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:
|
|
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:
Optionally, specify additional provider-specific parameters.
Important:
The following examples are for illustration purposes. For accurate and up-to-date information on additional parameters to use, refer to your third-party provider's documentation.{
"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" : "ollama",
"host" : "local",
"url" : "http://localhost:11434/api/embeddings",
"model" : "llama3"
}
{
"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 212-9 Additional REST Provider Parameter Details
Parameter | Description |
---|---|
|
Type of input to vectorize. |
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 an embedding withHello world
as input, by accessing the Generative AI embedding model:-- declare embedding parameters var params clob; begin :params := ' { "provider": "ocigenai", "credential_name": "OCI_CRED", "url": "https://generativeai.oci.example.com/embedText", "model": "embed.modelname", "batch_size": 10 }'; end; / -- get text embedding: PL/SQL example declare input clob; v vector; begin input := 'Hello world'; v := dbms_vector_chain.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; / -- get text embedding: select example select dbms_vector_chain.utl_to_embedding('Hello world', json(:params)) from dual;
The following examples use
UTL_TO_EMBEDDING
to generate an embedding withparrots.jpg
as input and modality asimage
, by accessing the Vertex AI's multimodal embedding model.-- declare embedding parameters var params clob; begin :params := ' { "provider": "vertexai", "credential_name": "VERTEXAI_CRED", "url": "https://LOCATION-aiplatform.googleapis.com/v1/projects/PROJECT/locations/LOCATION/publishers/google/models/", "model": "multimodalembedding:predict" }'; end; / -- get image embedding: PL/SQL example declare v vector; output clob; begin v := dbms_vector_chain.utl_to_embedding( to_blob(bfilename('VEC_DUMP', 'parrots.jpg')), 'image', json(:params)); output := vector_serialize(v); dbms_output.put_line('vector data=' || dbms_lob.substr(output, 100) || '...'); end; / -- get image embedding: select example select dbms_vector_chain.utl_to_embedding( to_blob(bfilename('VEC_DUMP', 'parrots.jpg')), 'image', json(:params));
End-to-end examples:
To run end-to-end example scenarios using
UTL_TO_EMBEDDING
, see Generate Embedding. -
The following example uses
UTL_TO_EMBEDDINGS
to generate embeddings with a PDF document (stored in thedocumentation_tab
table) as input, by calling an ONNX format model loaded into the database:SELECT et.* from documentation_tab dt,dbms_vector_chain.utl_to_embeddings( dbms_vector_chain.utl_to_chunks(dbms_vector_chain.utl_to_text(dt.data)), json(:embed_params)) et;
End-to-end examples:
To run end-to-end example scenarios using
UTL_TO_EMBEDDINGS
, see Perform Chunking With Embedding.
212.1.12 UTL_TO_GENERATE_TEXT
Use the DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT
chainable utility function to generate a text response for a given prompt or an image, by accessing third-party text generation models.
Purpose
To communicate with Large Language Models (LLMs) through natural language conversations. You can generate a textual answer, description, or summary for prompts and images, given as input to LLM-powered chat interfaces.
-
Prompt to Text:
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 command, such as "Summarize the following ...
", "Draft an email asking for ...
", or "Rewrite the following ...
", and can include results from a search. The LLM responds with a textual answer or description based on the specified task in the prompt.For this operation, this API makes a REST call to either a remote third-party provider (Cohere, Google AI, Hugging Face, Generative AI, OpenAI, or Vertex AI) or a local third-party provider (Ollama).
-
Image to Text:
You can also prompt with a media file, such as an image, to extract text from pictures or photos. You supply a text question as the prompt (such as "
What is this image about?
" or "How many birds are there in this painting?
") along with the image. The LLM responds with a textual analysis or description of the contents of the image.For this operation, this API makes a REST call to either a remote third-party provider (Google AI, Hugging Face, OpenAI, or Vertex AI) or a local third-party provider (Ollama).
WARNING:
Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.
Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle's responsibility or liability for any damages resulting from such access.
Syntax
This function accepts the input as CLOB
containing text data (for textual prompts) or as BLOB
containing media data (for media files such as images). It then processes this information to generate a new CLOB
containing the generated text.
-
Prompt to Text:
DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT ( DATA IN CLOB, PARAMS IN JSON default NULL ) return CLOB;
-
Image to Text:
DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT( TEXT_DATA IN CLOB, MEDIA_DATA IN BLOB, MEDIA_TYPE IN VARCHAR2 default 'image/jpeg', PARAMS IN JSON default NULL ) return CLOB;
DATA and TEXT_DATA
Specify the textual prompt as CLOB
for the DATA
or TEXT_DATA
clause.
Note:
Hugging Face uses an image captioning model that does not require a prompt, when giving an image as input. If you input a prompt along with an image, then the prompt will be ignored.MEDIA_DATA
Specify the BLOB
file, such as an image or a visual PDF file.
MEDIA_TYPE
Specify the image format for the given image or visual PDF file (BLOB
file) in one of the supported image data MIME types. For example:
-
For PNG:
image/png
-
For JPEG:
image/jpeg
-
For PDF:
application/pdf
Note:
For a complete list of the supported image formats, refer to your third-party provider's documentation.PARAMS
Specify the following input parameters in JSON format, depending on the service provider that you want to access for text generation:
{
"provider" : "<AI service provider>",
"credential_name" : "<credential name>",
"url" : "<REST endpoint URL for text generation service>",
"model" : "<text generation model name>",
"transfer_timeout": <maximum wait time for the request to complete>,
"<additional REST provider parameter>": "<REST provider parameter value>"
}
Table 212-10 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: For
For
|
|
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. Note: For a list of all supported REST endpoints, see Supported Third-Party Provider Operations and Endpoints. |
|
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:
Optionally, specify additional provider-specific parameters.
Important:
The following examples are for illustration purposes. For accurate and up-to-date information on additional parameters to use, refer to your third-party provider's documentation.{
"provider" : "cohere",
"credential_name": "COHERE_CRED",
"url" : "https://api.cohere.example.com/chat",
"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" : "ocigenai",
"credential_name" : "OCI_CRED",
"url" : "https://inference.generativeai.oci.example.com/actions/chat",
"model" : "generate-text-model",
"chatRequest" : {
"maxTokens" : 256
}
}
{
"provider" : "ollama",
"host" : "local",
"url" : "http://localhost:11434/api/generate",
"model" : "llama3"
}
{
"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 212-11 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. |
Examples
-
Prompt to Text:
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_chain.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_chain.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; /
End-to-end examples:
To run end-to-end example scenarios, see Generate Text Response.
-
Image to Text:
The following statements generate text by making a REST call to OpenAI. Here, the input is an image (
sample_image.jpeg
) along with the prompt "Describe this image?
".-- select example var input clob; var media_data blob; var media_type clob; var params clob; begin :input := 'Describe this image'; :media_data := load_blob_from_file('DEMO_DIR', 'sample_image.jpeg'); :media_type := 'image/jpeg'; :params := ' { "provider": "openai", "credential_name": "OPENAI_CRED", "url": "https://api.openai.example.com/chat/completions", "model": "generate.modelname", "max_tokens": 256 }'; end; / select dbms_vector_chain.utl_to_generate_text(:input, :media_data, :media_type, json(:params)); -- PL/SQL example declare input clob; media_data blob; media_type varchar2(32); params clob; output clob; begin input := 'Describe this image'; media_data := load_blob_from_file('DEMO_DIR', 'image_file'); media_type := 'image/jpeg'; params := ' { "provider": "openai", "credential_name": "OPENAI_CRED", "url": "https://api.openai.example.com/chat/completions", "model": "generate.modelname", "max_tokens": 256 }'; output := dbms_vector_chain.utl_to_generate_text( input, media_data, media_type, json(params)); dbms_output.put_line(output); if output is not null then dbms_lob.freetemporary(output); end if; if media_data is not null then dbms_lob.freetemporary(media_data); end if; exception when OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); DBMS_OUTPUT.PUT_LINE (SQLCODE); end; /
End-to-end examples:
To run end-to-end example scenarios, see Describe Image Content.
212.1.13 UTL_TO_TEXT
Use the DBMS_VECTOR_CHAIN.UTL_TO_TEXT
chainable utility function to convert an input document (for example, PDF, DOC, JSON, XML, or HTML) to plain text.
Purpose
To perform a file-to-text transformation by using the Oracle Text component (CONTEXT
) of Oracle Database.
Syntax
DBMS_VECTOR_CHAIN.UTL_TO_TEXT (
DATA IN CLOB | BLOB,
PARAMS IN JSON default NULL
) return CLOB;
DATA
This function accepts the input data type as CLOB
or BLOB
. It can read documents from a remote location or from files stored locally in the database tables.
It returns a plain text version of the document as CLOB
.
Oracle Text supports around 150 file types. For a complete list of all the supported document formats, see Oracle Text Reference.
PARAMS
Specify the following input parameter in JSON format:
{
"plaintext" : "true or false",
"charset" : "UTF8"
}
Table 212-12 Parameter Details
Parameter | Description |
---|---|
|
Plain text output. The default value for this parameter is If you do not want to return the document as plain text, then set this parameter to |
|
Character set encoding. Currently, only |
Example
select DBMS_VECTOR_CHAIN.UTL_TO_TEXT (
t.blobdata,
json('{
"plaintext": "true",
"charset" : "UTF8"
}')
) from tab t;
End-to-end example:
To run an end-to-end example scenario using this function, see Convert File to Text to Chunks to Embeddings Within Oracle Database.
212.1.14 UTL_TO_SUMMARY
Use the DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY
chainable utility function to generate a summary for textual documents.
Purpose
-
Oracle Database as the service provider: Uses the in-house implementation with Oracle Database, leveraging a document gist (or summary) generated by Oracle Text (default setting)
-
Third-party summarization model: Makes a REST API call to either a remote service provider (Cohere, Google AI, Hugging Face, Generative AI, OpenAI, or Vertex AI) or a local service provider (Ollama)
A summary is a short and concise extract with key features of a document that best represents what the document is about as a whole. A summary can be free-form paragraphs or bullet points based on the format that you specify.
WARNING:
Certain features of the database may allow you to access services offered separately by third-parties, for example, through the use of JSON specifications that facilitate your access to REST APIs.
Your use of these features is solely at your own risk, and you are solely responsible for complying with any terms and conditions related to use of any such third-party services. Notwithstanding any other terms and conditions related to the third-party services, your use of such database features constitutes your acceptance of that risk and express exclusion of Oracle's responsibility or liability for any damages resulting from such access.
Syntax
DBMS_VECTOR_CHAIN.UTL_TO_SUMMARY (
DATA IN CLOB,
PARAMS IN JSON default NULL
) return CLOB;
DATA
This function accepts the input data type in plain text as CLOB
.
It returns a summary of the input document also as CLOB
.
PARAMS
Specify summary parameters in JSON format, depending on the service provider that you want to use for document summarization.
{
"provider" : "database",
"glevel" : "<summary format>",
"numParagraphs": <number in the range 1-16>,
"maxPercent" : <number in the range 1-100>,
"num_themes" : <number in the range 1-50>,
"language" : "<name of the language>"
}
Table 212-13 Database Provider Parameter Details
Parameter | Description |
---|---|
|
Specify |
|
Format to display the summary:
|
|
Maximum number of document paragraphs (or sentences) selected for the summary. The default value is The |
|
Maximum number of document paragraphs (or sentences) selected for the summary, as a percentage of the total paragraphs (or sentences) in the document. The default value is The |
|
Number of theme summaries to produce. For example, if you specify The default value is |
|
Language name of your summary text, as listed in Supported Languages and Data File Locations. |
If using a third-party provider:
{
"provider" : "<AI service provider>",
"credential_name" : "<credential name>",
"url" : "<REST endpoint URL for summarization service>",
"model" : "<REST provider summarization model name>",
"transfer_timeout": <maximum wait time for the request to complete>,
"<additional REST provider parameter>": "<REST provider parameter value>"
}
Table 212-14 Third-Party Provider Parameter Details
Parameter | Description |
---|---|
|
Third-party service provider that you want to access to get the summary. A REST call is made to the specified provider to access its text summarization model. Specify one of the following values:
|
|
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. Note: For a list of all supported REST endpoints, see Supported Third-Party Provider Operations and Endpoints. |
|
Name of the third-party text summarization model in the form:
If the model name is not schema-qualified, then the schema of the procedure invoker is used. Note: For Generative AI, you must specify |
|
Maximum time to wait for the request to complete. The default value is |
Additional REST provider parameters:
Optionally, specify additional provider-specific parameters.
Important:
The following examples are for illustration purposes. For accurate and up-to-date information on additional parameters to use, refer to your third-party provider's documentation.{
"provider" : "cohere",
"credential_name" : "COHERE_CRED",
"url" : "https://api.cohere.example.com/summarize",
"model" : "summarize-model",
"length" : "medium",
"format" : "paragraph",
"temperature" : 1.0
}
{
"provider" : "googleai",
"credential_name" : "GOOGLEAI_CRED",
"url" : "https://googleapis.example.com/models/",
"model" : "summarize-model",
"generation_config": {
"temperature" : 0.9,
"topP" : 1,
"candidateCount" : 1,
"maxOutputTokens": 256
}
}
{
"provider" : "huggingface",
"credential_name" : "HF_CRED",
"url" : "https://api.huggingface.example.co/models/",
"model" : "summarize-model"
}
{
"provider" : "ocigenai",
"credential_name" : "OCI_CRED",
"url" : "https://generativeai.oci.example.com/summarizeText",
"model" : "summarize-model",
"length" : "MEDIUM",
"format" : "PARAGRAPH"
}
{
"provider" : "ollama",
"host" : "local",
"url" : "http://localhost:11434/api/generate",
"model" : "llama3"
}
{
"provider" : "openai",
"credential_name" : "OPENAI_CRED",
"url" : "https://api.openai.example.com",
"model" : "summarize-model",
"max_tokens" : 256,
"temperature" : 1.0
}
{
"provider" : "vertexai",
"credential_name" : "VERTEXAI_CRED",
"url" : "https://googleapis.example.com/models/",
"model" : "summarize-model",
"generation_config" : {
"temperature" : 0.9,
"topP" : 1,
"candidateCount" : 1,
"maxOutputTokens" : 256
}
}
Table 212-15 Additional REST Provider Parameter Details
Parameter | Description |
---|---|
|
Approximate length of the summary text:
Note: For Generative AI, you must enter this value in uppercase. |
|
Format to display the summary:
Note: For Generative AI, you must enter this value in uppercase. |
|
Degree of randomness used when generating output text, in the range of To generate the same output for a prompt, use Default temperature is Note: To summarize a text, start with the temperature set to |
|
How much to reuse the input in the summary:
Note: For Generative AI, you must enter this value in uppercase. |
|
Maximum number of tokens in the output text. |
|
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. |
Note:
When specifying thelength
, format
, and extractiveness
parameters for Generative AI, ensure to enter the values in uppercase letters.
Examples
These statements generate a summary from an extract on "Transactions", by accessing the Generative AI summarization model.
-- select example
var params clob;
exec :params := '
{
"provider": "ocigenai",
"credential_name": "OCI_CRED",
"url": "https://generativeai.oci.example.com/summarizeText",
"model": "summarize.modelname",
"temperature": "0.0",
"extractiveness": "LOW"
}';
select dbms_vector_chain.utl_to_summary(
'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. The transfer consists of the following separate operations:
1. Decrease the savings account.
2. Increase the checking account.
3. Record the transaction in the transaction journal.
Oracle Database guarantees that all three operations succeed or fail as a unit. For example, if a hardware failure prevents a statement in the transaction from executing, then the other statements must be rolled back.
Transactions set Oracle Database apart from a file system. If you perform an atomic operation that updates several files, and if the system fails halfway through, then the files will not be consistent. In contrast, a transaction moves an Oracle database from one consistent state to another. The basic principle of a transaction is "all or nothing": an atomic operation succeeds or fails as a whole.',
json(:params)) from dual;
-- PL/SQL example
declare
input clob;
params clob;
output clob;
begin
input := '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. The transfer consists of the following separate operations:
1. Decrease the savings account.
2. Increase the checking account.
3. Record the transaction in the transaction journal.
Oracle Database guarantees that all three operations succeed or fail as a unit. For example, if a hardware failure prevents a statement in the transaction from executing, then the other statements must be rolled back.
Transactions set Oracle Database apart from a file system. If you perform an atomic operation that updates several files, and if the system fails halfway through, then the files will not be consistent. In contrast, a transaction moves an Oracle database from one consistent state to another. The basic principle of a transaction is "all or nothing": an atomic operation succeeds or fails as a whole.';
params := '
{
"provider": "ocigenai",
"credential_name": "OCI_CRED",
"url": "https://generativeai.oci.example.com/summarizeText",
"model": "summarize.modelname",
"length": "MEDIUM",
"format": "PARAGRAPH",
"temperature": 1.0
}';
output := dbms_vector_chain.utl_to_summary(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;
/
End-to-end examples:
To run end-to-end example scenarios using this function, see Generate Summary.
212.2 Supported Languages and Data File Locations
These are the supported languages for which language data files are distributed by default in the specified directories.
Language Name | Abbreviation | Data File |
---|---|---|
AFRIKAANS |
af |
|
AMERICAN |
us |
|
ARABIC |
ar |
|
BASQUE |
eu |
|
BELARUSIAN |
be |
|
BRAZILIAN PORTUGUESE |
ptb |
|
BULGARIAN |
bg |
|
CANADIAN FRENCH |
frc |
|
CATALAN |
ca |
|
CROATIAN |
hr |
|
CYRILLIC SERBIAN |
csr |
|
CZECH |
cs |
|
DANISH |
dk |
|
DARI |
prs |
|
DUTCH |
nl |
|
EGYPTIAN |
eg |
|
ENGLISH |
gb |
|
ESTONIAN |
et |
|
FINNISH |
sf |
|
FRENCH |
f |
|
GALICIAN |
ga |
|
GERMAN |
d |
|
GERMAN DIN |
din |
|
GREEK |
el |
|
HEBREW |
iw |
|
HINDI |
hi |
|
HUNGARIAN |
hu |
|
ICELANDIC |
is |
|
INDONESIAN |
in |
|
ITALIAN |
i |
|
JAPANESE |
ja |
|
KOREAN |
ko |
|
LATIN AMERICAN SPANISH |
esa |
|
LATIN BOSNIAN |
lbs |
|
LATIN SERBIAN |
lsr |
|
LATVIAN |
lv |
|
LITHUANIAN |
lt |
|
MACEDONIAN |
mk |
|
MALAY |
ms |
|
MEXICAN SPANISH |
esm |
|
NORWEGIAN |
n |
|
NYNORSK |
nn |
|
PERSIAN |
fa |
|
POLISH |
pl |
|
PORTUGUESE |
pt |
|
ROMANIAN |
ro |
|
RUSSIAN |
ru |
|
SIMPLIFIED CHINESE |
zhs |
|
SLOVAK |
sk |
|
SLOVENIAN |
sl |
|
SPANISH |
e |
|
SWEDISH |
s |
|
THAI |
th |
|
TRADITIONAL CHINESE |
zht |
|
TURKISH |
tr |
|
UKRAINIAN |
uk |
|
URDU |
ur |
|
Related Topics