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.

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>" }

-----BEGIN RSA PRIVATE KEY-----
<private key string>
-----END RSA PRIVATE KEY-----

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

End-to-end examples:

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

Purpose

To create a vector index such as Hierarchical Navigable Small World (HNSW) vector index or Inverted File Flat (IVF) vector 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
); 

Parameters

Examples

• Specify neighbors and efConstruction for HNSW indexes:

  dbms_vector.create_index(
      'v_hnsw_01', 
      'vpt01', 
      'EMBEDDING', 
       NULL, 
       NULL, 
      'INMEMORY NEIGHBOR GRAPH', 
      'EUCLIDEAN', 
       95, 
      '{"type" : "HNSW", "neighbors" : 3, "efConstruction" : 4}');

• Specify the number of partitions for IVF indexes:

  dbms_vector.create_index(
      'V_IVF_01', 
      'vpt01', 
      'EMBEDDING', 
       NULL,
       NULL, 
      'NEIGHBOR PARTITIONS', 
      'EUCLIDEAN', 
       95, 
      '{"type" : "IVF", "partitions" : 5}');

Syntax

DBMS_VECTOR.DISABLE_CHECKPOINT('INDEX_USER',['INDEX_NAME']);

INDEX_USER

Specify the user name of the HNSW vector index owner.

INDEX_NAME

Specify the name of the HNSW vector index for which you want to disable the Checkpoint feature.

The INDEX_NAME clause is optional. If you do not specify the index name, then this procedure disables the Checkpoint feature for all HNSW vector indexes under the given user.

Examples

• Using both the index name and index user:

  DBMS_VECTOR.DISABLE_CHECKPOINT('VECTOR_USER','VIDX1');

• Using only the index user:

  DBMS_VECTOR.DISABLE_CHECKPOINT('VECTOR_USER');

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');

Syntax

DBMS_VECTOR.ENABLE_CHECKPOINT('INDEX_USER',['INDEX_NAME']);

INDEX_USER

Specify the user name of the HNSW vector index owner.

INDEX_NAME

Specify the name of the HNSW vector index for which you want to enable the Checkpoint feature.

The INDEX_NAME clause is optional. If you do not specify the index name, then this procedure enables the Checkpoint feature for all HNSW vector indexes under the given user.

Examples

• Using both the index name and index user:

  DBMS_VECTOR.ENABLE_CHECKPOINT('VECTOR_USER','VIDX1');

• Using only the index user:

  DBMS_VECTOR.ENABLE_CHECKPOINT('VECTOR_USER');

Syntax

DBMS_VECTOR.GET_INDEX_STATUS ('USER_NAME','INDEX_NAME');

USER_NAME

Specify the user name of the vector index owner.

INDEX_NAME

Specify the name of the vector index. You can query the index creation status for both Hierarchical Navigable Small World (HNSW) indexes and Inverted File Flat (IVF) indexes.

Usage Notes

• You can use the GET_INDEX_STATUS procedure only during a vector index creation.

• The Percentage value is shown in the output only for Hierarchical Navigable Small World (HNSW) indexes (and not for Inverted File Flat (IVF) indexes).

• Along with the DB_DEVELOPER_ROLE privilege, you must have read access to the VECSYS.VECTOR$INDEX$BUILD$ table.

• You can use the following query to view all auxiliary tables:

  select IDX_AUXILIARY_TABLES from vecsys.vector$index;

  • For HNSW indexes:

    rowid_vid_map stores the mapping between a row ID and vector ID. shared_journal_change_log stores the DML changes that are yet to be incorporated into an HNSW graph.

  • For IVF indexes:

    centroids stores the location for each centroid. centroid_partitions stores the best centroid for each vector.

• The possible values of Stage for HNSW vector indexes are:

  Value	Description
  HNSW Index Initialization	Initialization phase for the HNSW vector index creation
  HNSW Index Auxiliary Tables Creation	Creation of the internal auxiliary tables for the HNSW Neighbor Graph vector index
  HNSW Index Graph Allocation	Allocation of memory from the vector memory pool for the HNSW graph
  HNSW Index Loading Vectors	Loading of the base table vectors into the vector pool memory
  HNSW Index Graph Construction	Creation of the multi-layered HNSW graph with the previously loaded vectors
  HNSW Index Creation Completed	HNSW vector index creation finished

• The possible values of Stage for IVF vector indexes are:

  Value	Description
  IVF Index Initialization	Initialization phase for the IVF vector index creation
  IVF Index Centroids Creation	The K-means clustering phase that computes the cluster centroids on a sample of base table vectors
  IVF Index Centroid Partitions Creation	Centroids assignment phase for the base table vectors
  IVF Index Creation Completed	IVF vector index creation completed

Example

exec DBMS_VECTOR.GET_INDEX_STATUS('VECTOR_USER','VIDX_HNSW');

Index objn: 74745 
Stage: HNSW Index Loading Vectors 
Percentage: 80%

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

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

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

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

Syntax

• Using the number and type of vector dimensions that you want to store in your vector index.

  DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR(
      INDEX_TYPE        IN    VARCHAR2, 
      NUM_VECTORS       IN    NUMBER, 
      DIM_COUNT         IN    NUMBER, 
      DIM_TYPE          IN    VARCHAR2, 
      PARAMETER_JSON    IN    CLOB, 
      RESPONSE_JSON     OUT   CLOB);

• Using the table and vector column on which you want to create your vector index:

  DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR(
      TABLE_OWNER        IN    VARCHAR2, 
      TABLE_NAME         IN    VARCHAR2, 
      COLUMN_NAME        IN    VARCHAR2, 
      INDEX_TYPE         IN    VARCHAR2, 
      PARAMETER_JSON     IN    CLOB, 
      RESPONSE_JSON      OUT   CLOB);

Examples

• Using neighbors in the parameters list:

  SET SERVEROUTPUT ON;
  DECLARE
      response_json CLOB;
  BEGIN
      DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR(
          INDEX_TYPE=>'HNSW', 
          NUM_VECTORS=>10000, 
          DIM_COUNT=>768, 
          DIM_TYPE=>'FLOAT32', 
          PARAMETER_JSON=>'{"neighbors":32}', 
          RESPONSE_JSON=>response_json);
  END;
  /

  Result:

  Suggested vector memory pool size: 59918628 Bytes

• Using accuracy in the parameters list:

  SET SERVEROUTPUT ON;
  DECLARE
      response_json CLOB;
  BEGIN
      DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR(
          INDEX_TYPE=>'HNSW', 
          NUM_VECTORS=>10000, 
          DIM_COUNT=>768, 
          DIM_TYPE=>'FLOAT32', 
          PARAMETER_JSON=>'{"accuracy":90}', 
          RESPONSE_JSON=>response_json); 
  END;
  /

  Result:

  Suggested vector memory pool size: 53926765 Bytes

• Using the table and vector column on which you want to create the vector index:

  SET SERVEROUTPUT ON;
  DECLARE
      response_json CLOB;
  BEGIN
      DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR(
          'VECTOR_USER', 
          'VECTAB', 
          'DATA_VECTOR', 
          'HNSW', 
          RESPONSE_JSON=>response_json);
  END;
  /

  Example result:

  Using default accuracy: 90% 
  Suggested vector memory pool size: 76396251 Bytes

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.

DATA

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

Purpose

To rebuild a vector index such as Hierarchical Navigable Small World (HNSW) vector index or Inverted File Flat (IVF) vector index. In case only the idx_name is provided, it rebuilds the index using get_ddl. When all the parameters are provided, it performs a drop index followed by a call to dbms_vector.create_index().

Syntax

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

Examples

• Specify neighbors and efConstruction for HNSW indexes:

  dbms_vector.rebuild_index(
      'v_hnsw_01', 
      'vpt01', 
      'EMBEDDING', 
       NULL, 
       NULL, 
      'INMEMORY NEIGHBOR GRAPH', 
      'EUCLIDEAN', 
       95, 
      '{"type" : "HNSW", "neighbors" : 3, "efConstruction" : 4}');

• Specify the number of partitions for IVF indexes:

  dbms_vector.rebuild_index(
      'V_IVF_01', 
      'vpt01', 
      'EMBEDDING', 
       NULL,
       NULL, 
      'NEIGHBOR PARTITIONS', 
      'EUCLIDEAN', 
       95, 
      '{"type" : "IVF", "partitions" : 5}');

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.

Syntax

DBMS_VECTOR.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."
}

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>",
  ...
}

Additional REST provider parameters:

Optionally, specify additional provider-specific parameters for reranking.

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

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

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

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.UTL_TO_CHUNKS (
    DATA         IN  CLOB | VARCHAR2,
    PARAMS       IN  JSON  default  NULL
) return VECTOR_ARRAY_T;

DATA

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

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

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

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

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:

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

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

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;

End-to-end examples:

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

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 Database or a third-party service provider:

  • Oracle 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 (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.

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"
}

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>" 
}

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>" 
  }

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

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

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

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

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

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

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

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

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

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

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

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 your chosen remote third-party provider (Cohere, Generative AI, Google AI, Hugging Face, OpenAI, or Vertex AI) or 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 your chosen remote third-party provider (Google AI, Hugging Face, OpenAI, or Vertex AI) or local third-party provider (Ollama).

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.UTL_TO_GENERATE_TEXT (
      	DATA          IN CLOB,
      	PARAMS        IN JSON default NULL
  ) return CLOB;

• Image to Text:

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

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

PARAMS

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

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

Additional third-party provider parameters:

Optionally, specify additional provider-specific parameters.

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

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

Generative AI example:

{
  "provider"       : "ocigenai",
  "credential_name": "OCI_CRED",
  "url"            : "https://inference.generativeai.us-example.com/chat",
  "model"          : "cohere.command-r-16k",
  "chatRequest"    : {
                      "maxTokens"    : 256
                     }
}

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

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

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

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

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

Examples

• Prompt to Text:

  The following statements generate a text response by making a REST call to Generative AI. The prompt given here is "What is Oracle Text?".

  Here, the cohere.command-r-16k and meta.llama-3.1-70b-instruct models are used. 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.

  Using the cohere.command-r-16k model:

  -- select example
  
  var params clob;
  exec :params := '
  {
    "provider"       : "ocigenai",
    "credential_name": "OCI_CRED",
    "url"            : "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
    "model"          : "cohere.command-r-16k",
    "chatRequest"    : {
                        "maxTokens": 256
                       }
  }';
  
  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://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
    "model"          : "cohere.command-r-16k",
    "chatRequest"    : {
                        "maxTokens": 256
                       }
  }';
  
    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;
  /

  Using the meta.llama-3.1-70b-instruct model:

  -- select example
  
  var params clob;
  exec :params := '
  {
     "provider"       : "ocigenai",
     "credential_name": "OCI_CRED",
     "url"            : "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
     "model"          : "meta.llama-3.1-70b-instruct",
     "chatRequest"    : {
                         "topK" : 1
                        }
  }';
  
  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://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
     "model"          : "meta.llama-3.1-70b-instruct",
     "chatRequest"    : {
                         "topK" : 1
                        }
  }';
  
    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;
  /

  End-to-end examples:

  To run end-to-end example scenarios, see Generate Text Response.

• Image to Text:

  The following statements generate a text response 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.com/v1/chat/completions",
    "model"          : "gpt-4o-mini",
    "max_tokens"     : 60
  }';
  end;
  /
  
  select dbms_vector.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.com/v1/chat/completions",
    "model"          : "gpt-4o-mini",
    "max_tokens"     : 60
  }';
  
    output := dbms_vector.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.