How to Generate Embeddings with Gemini from PL/SQL

An embedding model inside Oracle Database is fast, free, and private, but it is small and reads mostly English. An AI provider's model, such as Google's gemini-embedding-001, is far larger, reads more than 100 languages and longer texts, and needs no model files on your server. The price is that every text travels to Google, every request costs time and a little money, and the provider can be busy or down.

This guide shows how to get Gemini embeddings from Oracle AI Database 26ai with DBMS_VECTOR_CHAIN: one at a time, switched by model name from a table, in batches for a whole table, through Gemini's REST API for options the package does not pass, and in the background so a busy provider never blocks an insert.

Code for This Guide

The examples are in the examples/ch06 folder of the Oracle AI code repository on GitHub, each with its output. They run in the sample help desk schema from setup/atlas.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

They make real requests to Google through a database credential named GEMINI_CRED, set up in how to call Gemini from Oracle Database with a stored credential. The in-database model they compare with, ALL_MINILM_L12_V2, is loaded in how to load an ONNX embedding model into Oracle Database.

Gemini or a Model in the Database

Which of the two finds more right answers in your data is a question for a test, shown in how to choose an embedding model.

Embed One Text with Gemini

DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING with the provider googleai sends text to Gemini and returns the vector. The parameters name the provider, the credential, the API address, and the model.

Syntax:

dbms_vector_chain.utl_to_embedding(data clob, params json) return vector

{ "provider"        : "googleai",
  "credential_name" : "credential",
  "url"             : "https://generativelanguage.googleapis.com/v1beta/models/",
  "model"           : "model_name" }

Other providers work the same way with their own provider name, address, and models, such as openai, cohere, or ocigenai.

Example:

set timing on
select vector_dimension_count(dbms_vector_chain.utl_to_embedding(
         'I cannot sign in to my account',
         json('{"provider": "googleai",
                "credential_name": "GEMINI_CRED",
                "url": "https://generativelanguage.googleapis.com/v1beta/models/",
                "model": "gemini-embedding-001"}'))) as gemini_dimensions
from   dual;

select vector_dimension_count(vector_embedding(all_minilm_l12_v2
         using 'I cannot sign in to my account' as data)) as minilm_dimensions
from   dual;
set timing off

Output:

   GEMINI_DIMENSIONS
____________________
                3072

Elapsed: 00:00:01.253

   MINILM_DIMENSIONS
____________________
                 384

Elapsed: 00:00:00.159

Gemini's vector has eight times as many dimensions and took over a second, the trip to Google and back. The in-database model answered in a fraction of a second. Your times depend on your network and the provider's load.

Keep Model Parameters in a Table

The JSON parameters are long, and repeating them in every query invites mistakes. Keep them in a table, one row per model, and refer to a model by a short name.

Example:

create table embedding_models (
  name        varchar2(30) constraint embedding_models_pk primary key,
  dimensions  number       not null,
  params      json         not null
);

insert into embedding_models values
  ('MINILM', 384,
   json('{"provider": "database", "model": "ALL_MINILM_L12_V2"}')),
  ('GEMINI', 3072,
   json('{"provider": "googleai",
          "credential_name": "GEMINI_CRED",
          "url": "https://generativelanguage.googleapis.com/v1beta/models/",
          "model": "gemini-embedding-001"}'));
commit;

select name, dimensions,
       json_value(params, '$.provider') as provider,
       json_value(params, '$.model')    as model
from   embedding_models;

Output:

Table EMBEDDING_MODELS created.

2 rows inserted.

Commit complete.

NAME         DIMENSIONS PROVIDER    MODEL
_________ _____________ ___________ _______________________
MINILM              384 database    ALL_MINILM_L12_V2
GEMINI             3072 googleai    gemini-embedding-001

One function then embeds with any model by its name.

Example:

create or replace function embed (
  p_text   in clob,
  p_model  in varchar2 default 'MINILM'
) return vector
is
  l_params json;
begin
  select params into l_params from embedding_models where name = p_model;
  return dbms_vector_chain.utl_to_embedding(p_text, l_params);
end;
/

select vector_dimension_count(embed('I cannot sign in'))           as minilm,
       vector_dimension_count(embed('I cannot sign in', 'GEMINI')) as gemini
from   dual;

Output:

Function EMBED compiled

   MINILM    GEMINI
_________ _________
      384      3072

Switching a feature to another model, or another provider, is now a matter of a name and a row in EMBEDDING_MODELS.

Embed a Table, One Row per Request

The simplest way to embed a table is an UPDATE that calls the function for each row.

Example:

alter table kb_articles add (gemini_embedding vector(3072, float32));

set timing on
update kb_articles
set    gemini_embedding = embed(title || '. ' || body, 'GEMINI');
set timing off
commit;

Output:

Table KB_ARTICLES altered.

24 rows updated.

Elapsed: 00:00:21.202

Commit complete.

Twenty-four articles took about 21 seconds: one request to Google per row, close to a second each. At that rate, a million rows would take more than a week.

Embed in Batches with UTL_TO_EMBEDDINGS

UTL_TO_EMBEDDINGS embeds many texts in one call and sends them to the provider in batches. It takes an array of JSON objects, each with an ID and a text, and returns an array of objects, each with the ID, the text, and the embedding.

Syntax:

dbms_vector_chain.utl_to_embeddings(data sys.vector_array_t, params json)
  return sys.vector_array_t

{"chunk_id": id, "chunk_data": "text"}                          -- input
{"embed_id": id, "embed_data": "text", "embed_vector": "[...]"} -- result

SYS.VECTOR_ARRAY_T is a collection of CLOB values. This small example shows the shape of one result.

Example:

declare
  l_results sys.vector_array_t;
begin
  l_results := dbms_vector_chain.utl_to_embeddings(
                 sys.vector_array_t('{"chunk_id": 1, "chunk_data": "I cannot sign in"}',
                                    '{"chunk_id": 2, "chunk_data": "Billed twice"}'),
                 json('{"provider": "database", "model": "ALL_MINILM_L12_V2"}'));
  dbms_output.put_line(substr(l_results(1), 1, 90) || '...');
end;
/

Output:

{"embed_id":1,"embed_data":"I cannot sign in","embed_vector":"[-4.73671546E-003,-3.3019106...

PL/SQL procedure successfully completed.

The embedding comes back as text, a JSON string of numbers in square brackets, which TO_VECTOR turns into a vector.

This example embeds all 400 tickets with Gemini in one call. It builds one JSON object per ticket with BULK COLLECT, calls UTL_TO_EMBEDDINGS, and updates each ticket from the results.

Example:

alter table tickets add (gemini_embedding vector(3072, float32));

set timing on
declare
  l_chunks   sys.vector_array_t;
  l_results  sys.vector_array_t;
  l_params   json;
begin
  select params into l_params from embedding_models where name = 'GEMINI';

  -- one JSON object per ticket: its ID and its text
  select json_object('chunk_id'   value ticket_id,
                     'chunk_data' value subject || '. ' || description
                     returning clob)
  bulk   collect into l_chunks
  from   tickets;

  l_results := dbms_vector_chain.utl_to_embeddings(l_chunks, l_params);

  -- each result says which ticket it belongs to
  forall i in 1 .. l_results.count
    update tickets
    set    gemini_embedding = to_vector(json_value(l_results(i), '$.embed_vector'
                                                   returning clob))
    where  ticket_id = json_value(l_results(i), '$.embed_id');

  dbms_output.put_line('Tickets embedded: ' || l_results.count);
end;
/
set timing off
commit;

Output:

Table TICKETS altered.

Tickets embedded: 400

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.570

Commit complete.

Four hundred embeddings in about 20 seconds, over ten times faster than one request per row. Two details matter:

• Results do not come back in input order. Match each one to its row by embed_id, never by position.
• A batch is all or nothing. If a request fails, the call raises an error and returns no embeddings at all.

The array must be built in PL/SQL. Building it in a single query with CAST(COLLECT(...) AS SYS.VECTOR_ARRAY_T) fails in 26ai with ORA-22814: Attribute or element value is larger than specified in type.

What It Costs

Providers charge per input token, and embedding models cost a small fraction of what generation costs. The 400 tickets average 135 characters, about 35 tokens each: some 14,000 tokens for the whole table, well under a cent. Each new ticket or search question adds a few dozen tokens.

Storage is the bigger cost. A FLOAT32 vector of 3,072 dimensions takes 12 KB, too large to stay inside the row, so it lives in a LOB segment.

Example:

-- the space the two embedding columns of TICKETS take, in the table and in LOB segments
select c.column_name,
       round(sum(s.bytes) / 1024 / 1024, 1) as mb
from   user_lobs c
join   user_segments s on s.segment_name in (c.segment_name, c.index_name)
where  c.table_name = 'TICKETS'
and    c.column_name in ('EMBEDDING', 'GEMINI_EMBEDDING')
group  by c.column_name
union all
select 'TICKETS (the table)', round(bytes / 1024 / 1024, 1)
from   user_segments
where  segment_name = 'TICKETS';

Output:

COLUMN_NAME                MB
______________________ ______
EMBEDDING                 0.3
GEMINI_EMBEDDING          8.3
TICKETS (the table)         2

The 384-dimension vectors are stored inside the rows of the 2 MB table, while Gemini's vectors take 8.3 MB of LOB storage for 400 rows, about 21 KB per ticket. At millions of rows the difference is gigabytes, and it grows again in a vector index.

Use Options the Package Does Not Pass

Gemini can return shorter vectors, such as 768 or 1,536 dimensions, and tune an embedding for its task: a stored document or a search question. UTL_TO_EMBEDDING puts extra parameters in the wrong place of the request, and Google rejects them.

Example:

select vector_dimension_count(dbms_vector_chain.utl_to_embedding(
         'I cannot sign in to my account',
         json('{"provider": "googleai",
                "credential_name": "GEMINI_CRED",
                "url": "https://generativelanguage.googleapis.com/v1beta/models/",
                "model": "gemini-embedding-001",
                "outputDimensionality": 768}'))) as dimensions
from   dual;

Output:

Error starting at line : 1
In command -
select vector_dimension_count(dbms_vector_chain.utl_to_embedding(
         'I cannot sign in to my account',
         json('{"provider": "googleai",
                "credential_name": "GEMINI_CRED",
                "url": "https://generativelanguage.googleapis.com/v1beta/models/",
                "model": "gemini-embedding-001",
                "outputDimensionality": 768}'))) as dimensions
from   dual
Error at Command Line : 1 Column : 31
Error report -
SQL Error: ORA-20000: Oracle Text error:
DRG-50857: oracle error in dbms_vector_chain.utl_to_embedding(clob)
ORA-20002: The provider returned an error - Invalid JSON payload received.
Unknown name "outputDimensionality": Cannot find field.
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.DBMS_VECTOR_CHAIN", line 976
ORA-06512: at line 1

For those options, call Gemini's REST API yourself. With APEX installed, APEX_WEB_SERVICE.MAKE_REST_REQUEST does it, using an APEX web credential that holds the key, so the key never appears in your code. Outside an APEX application, APEX_UTIL.SET_WORKSPACE first tells APEX which workspace's credentials to use.

Syntax:

apex_web_service.make_rest_request(
  p_url                   varchar2,
  p_http_method           varchar2,
  p_body                  clob     default empty_clob(),
  p_credential_static_id  varchar2 default null) return clob

This example asks Gemini's embedContent method for a 768-dimension embedding tuned for a search question (RETRIEVAL_QUERY), and turns the JSON array of numbers into a vector.

Example:

declare
  l_response  clob;
  l_vector    vector;
begin
  apex_util.set_workspace('ATLAS');      -- the workspace that owns the web credential

  apex_web_service.set_request_headers('Content-Type', 'application/json');
  l_response := apex_web_service.make_rest_request(
    p_url  => 'https://generativelanguage.googleapis.com/v1beta/models/'
              || 'gemini-embedding-001:embedContent',
    p_http_method => 'POST',
    p_body => '{"content": {"parts": [{"text": "I cannot sign in to my account"}]},
                "taskType": "RETRIEVAL_QUERY",
                "outputDimensionality": 768}',
    p_credential_static_id => 'credentials-for-gemini');

  dbms_output.put_line('HTTP status: ' || apex_web_service.g_status_code);
  dbms_output.put_line('Response:    ' || substr(replace(replace(l_response, chr(10)), ' '),
                                                 1, 70) || '...');

  -- the numbers are a JSON array: TO_VECTOR turns it into a vector
  l_vector := to_vector(json_query(l_response, '$.embedding.values' returning clob));
  dbms_output.put_line('Dimensions:  ' || vector_dimension_count(l_vector));
  dbms_output.put_line('Length:      ' || round(vector_norm(l_vector), 4));
end;
/

Output:

HTTP status: 200
Response:    {"embedding":{"values":[0.030464923,0.014785889,-0.011097522,-0.042740...
Dimensions:  768
Length:      .5801

PL/SQL procedure successfully completed.

The vector has 768 dimensions and a length of 0.58: Gemini normalizes only its full-size vectors. COSINE distance ignores length, so searches with COSINE work unchanged, while DOT would need the vectors normalized first. Embed stored documents with RETRIEVAL_DOCUMENT and questions with RETRIEVAL_QUERY, and store the vectors in a VECTOR(768, FLOAT32) column.

The web credential is created with Gemini's Generative AI service in APEX; see generative AI in Oracle APEX: services, agents, and tools.

When the Provider Fails

A model in the database fails only when the database does. A provider fails for its own reasons:

The in-database model returns NULL for NULL text; Gemini raises an error instead.

Example:

select embed(null, 'GEMINI') from dual;

Output:

Error starting at line : 1
In command -
select embed(null, 'GEMINI') from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-20000: Oracle Text error:
DRG-50857: oracle error in dbms_vector_chain.utl_to_embedding(clob)
ORA-20002: The provider returned an error - *
BatchEmbedContentsRequest.requests[0].content.parts[0].data: required oneof
field 'data' must have one initialized field
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.DBMS_VECTOR_CHAIN", line 976
ORA-06512: at "ATLAS.EMBED", line 9
ORA-06512: at line 1

Each failure surfaces as ORA-20002: The provider returned an error, followed by the provider's message.

Embed in the Background

Embedding new rows in a trigger works well with an in-database model. With a provider, every insert would wait for Google, and every provider failure would be a failed insert: a customer's ticket lost because Gemini was busy. Separate saving the row from embedding it. Save at once, and let a procedure embed the rows that have no embedding yet, in batches, whenever it runs.

Example:

create or replace procedure embed_pending (p_batch_size in pls_integer default 100)
is
  l_chunks   sys.vector_array_t;
  l_results  sys.vector_array_t;
  l_params   json;
begin
  select params into l_params from embedding_models where name = 'GEMINI';

  loop
    -- the next batch of tickets that have no Gemini embedding yet
    select json_object('chunk_id'   value ticket_id,
                       'chunk_data' value subject || '. ' || description
                       returning clob)
    bulk   collect into l_chunks
    from   tickets
    where  gemini_embedding is null
    fetch  first p_batch_size rows only;

    exit when l_chunks.count = 0;

    begin
      l_results := dbms_vector_chain.utl_to_embeddings(l_chunks, l_params);
    exception
      when others then
        -- leave the rows for the next run: the provider may be busy or down
        dbms_output.put_line('Gemini failed, will retry later: '
                             || substr(sqlerrm, 1, 60));
        exit;
    end;

    forall i in 1 .. l_results.count
      update tickets
      set    gemini_embedding = to_vector(json_value(l_results(i), '$.embed_vector'
                                                     returning clob))
      where  ticket_id = json_value(l_results(i), '$.embed_id');

    dbms_output.put_line('Tickets embedded: ' || l_results.count);
    commit;
  end loop;
end;
/

insert into tickets (ticket_id, customer_id, product_id, subject, description,
                     priority, status, category, channel, created_at)
values (9002, 1, 2, 'Refund for a double payment',
        'We paid invoice 1043 twice by mistake. Please refund one of the payments.',
        'Normal', 'Open', 'Billing', 'Portal', systimestamp);
commit;

exec embed_pending

select ticket_id,
       vector_dimension_count(embedding)        as minilm,
       vector_dimension_count(gemini_embedding) as gemini
from   tickets
where  ticket_id = 9002;

exec embed_pending

Output:

Procedure EMBED_PENDING compiled

1 row inserted.

Commit complete.

Tickets embedded: 1

PL/SQL procedure successfully completed.

   TICKET_ID    MINILM    GEMINI
____________ _________ _________
        9002       384      3072

PL/SQL procedure successfully completed.

The new ticket got its Gemini embedding when EMBED_PENDING ran, and the second run found nothing left to do. If Gemini fails, the procedure stops and leaves the rows for the next run. Meanwhile, searches can skip rows without an embedding, or fall back on the in-database embedding every row has. Schedule the procedure every minute with DBMS_SCHEDULER, and log failures to a table instead of printing them.

Conclusion

DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING with the googleai provider embeds text with gemini-embedding-001 from SQL and PL/SQL, about a second per request. Keep each model's parameters in a table and switch models by name, embed whole tables with UTL_TO_EMBEDDINGS and match results by embed_id, call the REST API directly for options such as shorter vectors, and embed in the background so a busy provider never blocks your users.