UTL_TO_EMBEDDING and UTL_TO

UTL_TO_EMBEDDING and UTL_TO_EMBEDDINGS

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

Purpose

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

Text to Vector:

You can perform a text-to-embedding transformation by accessing either Oracle AI Database or a third-party service provider:
- Oracle AI Database as the service provider (default setting):
  
  This API calls an ONNX format embedding model that you load into the database.
- Third-party embedding model:
  
  This API makes a REST API call to your chosen remote service provider (Cohere, Generative AI, Google AI, Hugging Face, OpenAI, or Vertex AI) or local service provider (Private AI, Ollama).
Image to Vector:

You can also perform an image-to-embedding transformation. This API makes a REST call to your chosen image embedding model or multimodal embedding model by Vertex AI. Note that currently Vertex AI is the only supported service provider for this operation.

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

Image to Vector:

DBMS_VECTOR.UTL_TO_EMBEDDING (
    DATA           IN BLOB,
    MODALITY       IN VARCHAR2,
    PARAMS         IN JSON default NULL
) return VECTOR;

DATA

Text to Vector:

UTL_TO_EMBEDDING accepts the input as CLOB containing textual data (text strings or small documents). It then converts the text to a single embedding (VECTOR).

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

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

UTL_TO_EMBEDDING accepts the input as BLOB containing media data for media files such as images. It then converts the image input to a single embedding (VECTOR).

A generated embedding output includes:

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

Where,

embed_id displays the ID number of each embedding.
embed_data displays the input text that is transformed into embeddings.
embed_vector displays the generated vector representations.

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.

If using Oracle AI Database as the provider:

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

Table 12-13 Database Provider Parameter Details

Parameter Description

Parameter	Description
`provider`	Specify `database` (default setting) to use Oracle AI Database as the provider. With this setting, you must load an ONNX format embedding model into the database.
`model`	User-specified name under which the imported ONNX embedding model is stored in Oracle AI Database. If you do not have an embedding model in ONNX format, then perform the steps listed in Convert Pretrained Models to ONNX Format.

provider

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

model

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

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

If using a third-party provider:

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

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>,
  "max_count": "<maximum calls to the AI service provider>",
  "<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>",
  "max_count": "<maximum calls to the AI service provider>",
  "<additional REST provider parameter>": "<REST provider parameter value>" 
}

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

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

Table 12-14 Third-Party Provider Parameter Details

Parameter	Description
`provider`	Third-party service provider that you want to access for this operation. A REST call is made to the specified provider to access its embedding model. For image input, specify `vertexai`. For text input, specify one of the following values: `cohere` `googleai` `huggingface` `ocigenai` `openai` `vertexai` `mistralai` `ollama` `privateai`
`credential_name`	Name of the credential in the form: `schema.credential_name` A credential name holds authentication credentials to enable access to your provider for making REST API calls. You need to first set up your credential by calling the `DBMS_VECTOR.CREATE_CREDENTIAL` helper function to create and store a credential, and then refer to the credential name here. See CREATE_CREDENTIAL.
`host`	When using a local service provider, `host` can be used to disable credential by setting the value to `local`. When the `host` parameter is specified, `credential_name` should be omitted. The `host` parameter is applicable only if `provider` is one of the following: `openai` `ollama` `privateai`
`url`	URL of the third-party provider endpoint for each REST call, as listed in Supported Third-Party Provider Operations and Endpoints.
`model`	Name of the third-party embedding model in the form: `schema.model_name` If you do not specify a schema, then the schema of the procedure invoker is used. Note: For Generative AI, all the supported third-party models are listed in Supported Third-Party Provider Operations and Endpoints. If `privateai` is specified as the `provider`, `model` is not a required parameter. If `model` is not provided, the default model for `privateai` is used. Omitting `model` with any other `provider` value results in an error. For accurate results, ensure that the chosen text embedding model matches the vocabulary file used for chunking. If you are not using a vocabulary file, then ensure that the input length is defined within the token limits of your model. To get image embeddings, you can use any image embedding model or multimodal embedding model supported by Vertex AI. Multimodal embedding is a technique that vectorizes data from different modalities such as text and images. When using a multimodal embedding model to generate embeddings, ensure that you use the same model to vectorize both types of content (text and images). By doing so, the resulting embeddings are compatible and situated in the same vector space, which allows for effective comparison between the two modalities during similarity searches.
`transfer_timeout`	Maximum time to wait for the request to complete. The default value is `60` seconds. You can increase this value for busy web servers.
`batch_size`	Maximum number of vectors to request at a time. For example, for a batch size of `50`, if `100` chunks are passed, then this API sends two requests with an array of `50` strings each. If `30` chunks are passed (which is lesser than the defined batch size), then the API sends those in a single request. For REST calls, it is more efficient to send a batch of inputs at a time rather than requesting a single input per call. Increasing the batch size can provide better performance, whereas reducing the batch size may reduce memory and data usage, especially if your provider has a rate limit. The default or maximum allowed value depends on the third-party provider settings.
`max_count`	Maximum number of times the API can be called for a given third-party provider. When set to an integer n, `max_count` stops the execution of the API for the given provider beyond n times. This prevents accidental calling of a third-party over some limit, for example to avoid surpassing the service amount that you have purchased.

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

Table 12-15 Additional REST Provider Parameter Details

Parameter	Description
`input_type`	Type of input to vectorize.

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

Important:

The following examples are for illustration purposes. For accurate and up-to-date information on the parameters to use, refer to your third-party provider's documentation.
For a list of all supported REST endpoint URLs, see Supported Third-Party Provider Operations and Endpoints.
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.

Cohere example:

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

Generative AI example:

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

Google AI example:

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

Hugging Face example:

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

Ollama example:

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

OpenAI example:

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

OpenAI-compatible provider example:

Note:

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

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

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

Vertex AI example:

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

Mistral AI example:

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

Private AI example:

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

Examples

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

UTL_TO_EMBEDDING:

Text to vector using Generative AI:

The following examples use UTL_TO_EMBEDDING to generate an embedding with Hello world as the input.

Here, the cohere.embed-english-v3.0 model is used by accessing Generative AI as the provider. You can replace the model value with any other supported model that you want to use with Generative AI, as listed in Supported Third-Party Provider Operations and Endpoints.

-- declare embedding parameters

var params clob;

begin
 :params := '
{ 
  "provider": "ocigenai",
  "credential_name": "OCI_CRED", 
  "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
  "model": "cohere.embed-english-v3.0",
  "batch_size": 10
}';
end;
/

-- get text embedding: PL/SQL example

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

  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;
/

-- get text embedding: select example

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

Image to vector using Vertex AI:

The following examples use UTL_TO_EMBEDDING to generate an embedding by accessing the Vertex AI's multimodal embedding model.

Here, the input is parrots.jpg, VEC_DUMP is a local directory that stores the parrots.jpg file, and the modality is specified as image.

-- 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.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.utl_to_embedding(
  to_blob(bfilename('VEC_DUMP', 'parrots.jpg')), 'image', json(:params));

Text to vector using in-database embedding model:

The following example uses UTL_TO_EMBEDDING to generate a vector embedding by calling an ONNX format embedding model (doc_model) loaded into Oracle AI Database.

Here, the provider is database, and the input is hello.
```
var params clob; 
exec :params := '{"provider":"database", "model":"doc_model"}';

select dbms_vector.utl_to_embedding('hello', json(:params)) from dual;
```
For complete example, see Convert Text String to Embedding Within Oracle AI Database.

Text to vector using Private AI with HTTP and HTTPS:

The first part of this example demonstrates how to use Private AI if it is configured for HTTPS with a credential. It uses UTL_TO_EMBEDDING to generate an embedding with a string as input. The provider is specified as privateai to indicate that the Private AI Services Container should be used as the local REST endpoint provider.

For more information about the Private AI Services Container, see Oracle Private AI Services Container User's Guide.

define host=localhost
define port=9092
define wallet_path=<path to wallet>
define wallet_password=<wallet password>

exec utl_http.set_wallet('file:&wallet_path', '&wallet_password');

declare
  input clob;
  v vector;
begin
  input := 'The quick brown fox jumped over the fence.';
  v := dbms_vector.utl_to_embedding(
    input,
    json('{"provider": "privateai",
      "url": "https://&host:&port/v1/embeddings",
      "credential_name": "ORACLEAI_CRED",
      "model": "all-MiniLM-L12-v2" }'));
  dbms_output.put_line(vector_serialize(v));
exception
  when OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    DBMS_OUTPUT.PUT_LINE (SQLCODE);
end;
/

The second part of this example implements a similar embedding generation but uses Private AI configured for HTTP without a credential. Note the use of host=local to skip passing a credential.

define host=localhost
define port=9091

declare
  input clob;
  v vector;
begin
  input := 'The quick brown fox jumped over the fence.';
  v := dbms_vector.utl_to_embedding(
    input,
    json('{"provider": "privateai",
      "url": "http://&host:&port/v1/embeddings",
      "host": "local",
      "model": "all-MiniLM-L12-v2" }'));
  dbms_output.put_line(vector_serialize(v));
exception
  when OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    DBMS_OUTPUT.PUT_LINE (SQLCODE);
end;
/

Text to vector using Private AI, end-to-end:

This example demonstrates how to use Private AI to generate vector embeddings with UTL_TO_EMBEDDING, including creating a wallet, adding a signed certificate, and connecting to a host before generating text embeddings.

For more information about the Private AI Services Container, see Oracle Private AI Services Container User's Guide.

Use the command-line tool orapki to create an Oracle wallet and add the signed certificate.

mkdir /path/to/wallet
orapki wallet create -wallet /path/to/wallet -pwd <walletpassword>
orapki wallet add -wallet /path/to/wallet -trusted_cert -cert cert.pem -pwd <walletpassword>

Add permissions to connect to a host and access the wallet.

sqlplus / as sysdba

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'your-fully-qualified-hostname',
        lower_port => 8080,
        upper_port => 8080,
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'vectordb',
                           principal_type => xs_acl.ptype_db));
END;
/


BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE(
        wallet_path => 'file:/path/to/wallet',
        ace => xs$ace_type(
           privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),
           principal name => 'vectordb',
           principal_type => xs_acl.ptype_db));
END;
/

Now you can get the text embeddings:

sqlplus vectordb/vectord@pdb

-- create credential

exec dbms_vector.drop_credential('ORACLEAI_CRED');

declare
  jo json_object_t;
begin
  jo := json_object_t();
  jo.put('access_token', '<api key>');
  dbms_vector.create_credential(
    credential_name   => 'ORACLEAI_CRED',
    params            => json(jo.to_string));
end;
/

set serveroutput on

-- set wallet
exec utl_http.set_wallet('file:/path/to/wallet', '<walletpassword>');
-- get text embeddings
declare
  input clob;
  v vector;
begin
  input := 'The quick brown fox jumped over the fence.';
  v := dbms_vector.utl_to_embedding(
    input,
    json('{"provider": "privateai",
      "url": "https://<host>:<port>/v1/embeddings",
      "credential_name": "ORACLEAI_CRED",
      "model": "all-MiniLM-L12-v2" }'));
  dbms_output.put_line(vector_serialize(v));
exception
  when OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    DBMS_OUTPUT.PUT_LINE (SQLCODE);
end;
/

End-to-end examples:

To run various end-to-end example scenarios using UTL_TO_EMBEDDING, see Generate Embedding.

UTL_TO_EMBEDDINGS:

Text to vector using in-database embedding model:

The following example uses UTL_TO_EMBEDDINGS to generate an array of embeddings by calling an ONNX format embedding model (doc_model) loaded into Oracle AI Database.

Here, the provider is database, and the input is a PDF document stored in the documentation_tab table. As you can see, you first use UTL_TO_CHUNKS to split the data into smaller chunks before passing in to UTL_TO_EMBEDDINGS.
```
CREATE TABLE doc_chunks as
(select dt.id doc_id, et.embed_id, et.embed_data, to_vector(et.embed_vector) embed_vector
 from
   documentation_tab dt,
   dbms_vector.utl_to_embeddings(
       dbms_vector.utl_to_chunks(dbms_vector.utl_to_text(dt.data), json('{"normalize":"all"}')),
       json('{"provider":"database", "model":"doc_model"}')) t,
   JSON_TABLE(t.column_value, '$[*]' COLUMNS (embed_id NUMBER PATH '$.embed_id', embed_data VARCHAR2(4000) PATH '$.embed_data', embed_vector CLOB PATH '$.embed_vector')) et
);
```
For complete example, see SQL Quick Start Using a Vector Embedding Model Uploaded into the Database.
End-to-end examples:

To run various end-to-end example scenarios using UTL_TO_EMBEDDINGS, see Perform Chunking With Embedding.

Parent topic: DBMS_VECTOR