How to Generate Embeddings in SQL with VECTOR_EMBEDDING

Once an embedding model is loaded into Oracle AI Database 26ai, computing an embedding is a SQL function call. Embedding every row of a table is one UPDATE, a semantic search is one query, and no text leaves the database.

This guide shows how to use VECTOR_EMBEDDING: embed text, fill a vector column, run a first semantic search, keep embeddings current with a trigger, call the model from PL/SQL, and avoid the token limit that silently cuts long texts.

Code for This Guide

The examples are in the examples/ch05 folder of the Oracle AI code repository on GitHub, files 04 to 15, each with its output. They use the sample help desk schema ATLAS from setup/atlas, with 400 tickets and 24 knowledge base articles.

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 also need the embedding model ALL_MINILM_L12_V2 (384 dimensions) loaded into the schema, as shown in how to load an ONNX embedding model into Oracle Database.

VECTOR_EMBEDDING

VECTOR_EMBEDDING runs an embedding model on a value and returns the vector. It is a SQL function, so it works in a select list, an UPDATE, a WHERE clause, or an ORDER BY.

Syntax:

vector_embedding( model_name using expression as input_name )

Example:

select vector_dimension_count(v)  as dimensions,
       vector_dimension_format(v) as format,
       round(vector_norm(v), 4)   as length,
       substr(from_vector(v returning clob), 1, 44) || '...' as first_numbers
from  (select vector_embedding(all_minilm_l12_v2
                using 'I cannot sign in to my account' as data) as v
       from   dual);

Output:

   DIMENSIONS FORMAT        LENGTH FIRST_NUMBERS
_____________ __________ _________ __________________________________________________
          384 FLOAT32            1 [1.2992369E-002,-1.31205954E-002,5.19138714E...

The vector has 384 FLOAT32 dimensions and a length of 1. The model returns normalized vectors, so COSINE and DOT distances rank them identically.

Two Common Mistakes

The name after AS must be the model's input name, and the model must exist in the schema, or be qualified with its owner.

Example:

select vector_embedding(all_minilm_l12_v2 using 'I cannot sign in' as text) from dual;

select vector_embedding(minilm using 'I cannot sign in' as data) from dual;

Output:

Error starting at line : 1
In command -
select vector_embedding(all_minilm_l12_v2 using 'I cannot sign in' as text) from dual
Error at Command Line : 1 Column : 82
Error report -
SQL Error: ORA-54421: Missing mining attribute: DATA

Error starting at line : 3
In command -
select vector_embedding(minilm using 'I cannot sign in' as data) from dual
Error at Command Line : 3 Column : 71
Error report -
SQL Error: ORA-40284: model does not exist

ORA-54421 names the input the model expected. ORA-40284 means no model of that name is visible to the session.

See Meaning in the Distances

This example embeds five sentences and measures the cosine distance of each from a sign-in sentence and from a billing sentence.

Example:

create table sentences (id number, text varchar2(200), v vector(384, float32));

insert into sentences (id, text) values (1, 'I cannot sign in to my account');
insert into sentences (id, text) values (2, 'Login keeps saying invalid credentials');
insert into sentences (id, text) values (3, 'We were billed twice this month');
insert into sentences (id, text) values (4, 'Our card shows two identical payments');
insert into sentences (id, text) values (5, 'The mobile app closes right after it opens');

update sentences
set    v = vector_embedding(all_minilm_l12_v2 using text as data);
commit;

select s.id, s.text,
       round(vector_distance(s.v, a.v, cosine), 3) as from_sign_in,
       round(vector_distance(s.v, b.v, cosine), 3) as from_billed_twice
from   sentences s, sentences a, sentences b
where  a.id = 1 and b.id = 3
order  by s.id;

Output:

Table SENTENCES created.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

5 rows updated.

Commit complete.

   ID TEXT                                             FROM_SIGN_IN    FROM_BILLED_TWICE
_____ _____________________________________________ _______________ ____________________
    1 I cannot sign in to my account                              0                0.851
    2 Login keeps saying invalid credentials                  0.358                0.818
    3 We were billed twice this month                         0.851                    0
    4 Our card shows two identical payments                   0.811                 0.45
    5 The mobile app closes right after it opens              0.765                0.949

"Our card shows two identical payments" is 0.45 from "We were billed twice this month", and everything else is above 0.8, though the two sentences share no words. With Gemini's embedding model the same pair is 0.199 apart: the numbers depend on the model, but the order is the same. Judge distances by their order and gaps, and compare only distances from the same model.

Embed a Table

Store each embedding next to its text, in a VECTOR column declared with the model's size. What to embed is a design choice: a ticket's meaning is in its subject and description together, so both are embedded, joined by a period.

Example:

alter table tickets add (embedding vector(384, float32));

set timing on
update tickets
set    embedding = vector_embedding(all_minilm_l12_v2
                     using subject || '. ' || description as data);
set timing off
commit;

Output:

Table TICKETS altered.

400 rows updated.

Elapsed: 00:00:06.547

Commit complete.

About 6.5 seconds for 400 tickets, some 16 milliseconds each, on a laptop with no network call. A server with more CPUs can embed in parallel.

The knowledge base articles get the same treatment, embedded with their title and body.

Example:

alter table kb_articles add (embedding vector(384, float32));

update kb_articles
set    embedding = vector_embedding(all_minilm_l12_v2 using title || '. ' || body as data);
commit;

select count(*) as articles,
       count(case when embedding is not null then 1 end) as embedded
from   kb_articles;

Output:

Table KB_ARTICLES altered.

24 rows updated.

Commit complete.

   ARTICLES    EMBEDDED
___________ ___________
         24          24

Note how the example counts embeddings. COUNT(embedding) fails with ORA-22849, because COUNT does not accept a vector column. Count the rows where the vector IS NOT NULL instead.

Run a First Semantic Search

Embed the question with the same model, then order the rows by their distance from it.

Example:

with q as (select vector_embedding(all_minilm_l12_v2
                    using 'I was charged two times for one invoice' as data) as v
           from   dual)
select t.ticket_id, t.subject,
       round(vector_distance(t.embedding, q.v, cosine), 3) as distance
from   tickets t, q
order  by distance
fetch  first 5 rows only;

Output:

   TICKET_ID SUBJECT                        DISTANCE
____________ ___________________________ ___________
         267 Invoice paid two times            0.174
         214 Charged twice this month          0.185
         104 Invoice paid two times            0.192
         375 Invoice paid two times            0.202
          93 Invoice paid two times            0.232

All five tickets are about duplicate charges, in other words than the question: "paid two times", "charged twice". The WITH clause embeds the question once, and the query compares it with every ticket. That is instant on 400 rows; a vector index keeps it fast on millions.

The same idea finds the article that answers a question, the retrieval step of RAG. CROSS APPLY runs the subquery once per question.

Example:

with questions (question) as (
  values ('The app shuts down as soon as I open it'),
         ('How do I get a copy of last month''s bill?'),
         ('Someone signed in from another country'))
select q.question, a.article_id || ' ' || a.title as nearest_article
from   questions q
cross  apply (select article_id, title
              from   kb_articles
              order  by vector_distance(embedding,
                          vector_embedding(all_minilm_l12_v2 using q.question as data),
                          cosine)
              fetch  first 1 row only) a;

Output:

QUESTION                                     NEAREST_ARTICLE
____________________________________________ ________________________________________________
The app shuts down as soon as I open it      KB-401 Mobile app crashes on startup in 3.9.0
How do I get a copy of last month's bill?    KB-202 Downloading invoices and receipts
Someone signed in from another country       KB-105 Responding to a suspicious sign-in

"Shuts down as soon as I open it" shares no words with "crashes on startup", yet each question found the right article.

Keep Embeddings Current with a Trigger

An embedding is derived data: when the text changes, the embedding must change too, and a new row needs one at once. The natural trigger would fire on UPDATE OF subject, description, but DESCRIPTION is a CLOB, and a CLOB cannot be named in UPDATE OF. The trigger fires on every insert and update and compares the text itself.

Example:

create or replace trigger tickets_embedding_trg
before insert or update of subject, description on tickets
for each row
begin
  null;
end;
/

create or replace trigger tickets_embedding_trg
before insert or update on tickets
for each row
begin
  if inserting
     or :new.subject <> :old.subject
     or dbms_lob.compare(:new.description, :old.description) <> 0
  then
    select vector_embedding(all_minilm_l12_v2
             using :new.subject || '. ' || :new.description as data)
    into   :new.embedding
    from   dual;
  end if;
end;
/

insert into tickets (ticket_id, customer_id, product_id, subject, description,
                     priority, status, category, channel, created_at)
values (9001, 1, 2, 'Payment taken twice',
        'Our bank statement shows the same Atlas payment two times this month.',
        'High', 'Open', 'Billing', 'Email', systimestamp);

select ticket_id, vector_dimension_count(embedding) as dimensions
from   tickets
where  ticket_id = 9001;

rollback;

Output:

create or replace trigger tickets_embedding_trg
*
ERROR at line 1:
ORA-25006: cannot specify this column in UPDATE OF clause

Trigger TICKETS_EMBEDDING_TRG compiled

1 row inserted.

   TICKET_ID    DIMENSIONS
____________ _____________
        9001           384

Rollback complete.

DBMS_LOB.COMPARE returns 0 when two CLOBs are equal, so changing a ticket's status or priority does not recompute its embedding. The trigger adds about 16 milliseconds to each insert, which a help desk never notices. For bulk loads of millions of rows, disable it and embed afterward in one UPDATE or in batches.

When embedding is slow, for example with a provider's model over the network, set a flag in the trigger instead and let a scheduler job embed the flagged rows every minute.

Call the Model from PL/SQL

VECTOR_EMBEDDING is SQL, not PL/SQL: in a PL/SQL expression it does not compile. Wrap it in a SELECT ... INTO, as in this function, which also keeps the model name in one place.

Example:

declare
  v vector;
begin
  v := vector_embedding(all_minilm_l12_v2 using 'I cannot sign in' as data);
end;
/

create or replace function embed (p_text in clob) return vector
is
  v vector(384, float32);
begin
  select vector_embedding(all_minilm_l12_v2 using p_text as data) into v from dual;
  return v;
end;
/

select article_id, title
from   kb_articles
order  by vector_distance(embedding, embed('My invoice was charged two times'), cosine)
fetch  first 2 rows only;

Output:

  v := vector_embedding(all_minilm_l12_v2 using 'I cannot sign in' as data);
                                                       *
ERROR at line 4:
ORA-06550: line 4, column 56:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
   . ) @ %

Function EMBED compiled

ARTICLE_ID    TITLE
_____________ ________________________________
KB-201        Duplicate charges and refunds
KB-205        Custom fields on invoices

Queries now call embed('...'), and switching to another model means changing one function.

UTL_TO_EMBEDDING

DBMS_VECTOR.UTL_TO_EMBEDDING is the PL/SQL alternative. With the provider database, it runs a loaded model; the model is a value in a JSON parameter, so code can choose the model, or even switch to a provider, at run time, as shown in how to generate embeddings with Gemini from PL/SQL.

Syntax:

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

Example:

select round(vector_distance(
         dbms_vector.utl_to_embedding('I cannot sign in to my account',
           json('{"provider": "database", "model": "ALL_MINILM_L12_V2"}')),
         vector_embedding(all_minilm_l12_v2
                          using 'I cannot sign in to my account' as data)),
       6) as distance
from   dual;

declare
  v vector;
begin
  v := dbms_vector.utl_to_embedding('I cannot sign in to my account',
         json('{"provider": "database", "model": "ALL_MINILM_L12_V2"}'));
  dbms_output.put_line('Dimensions: ' || vector_dimension_count(v));
end;
/

Output:

   DISTANCE
___________
          0

Dimensions: 384

PL/SQL procedure successfully completed.

A distance of 0: both functions run the same model and return the same vector, and UTL_TO_EMBEDDING also works in a plain PL/SQL assignment.

NULL Text and Virtual Columns

The embedding of NULL is NULL, and so is the embedding of an empty string, which Oracle treats as NULL. An embedding also cannot be a virtual column.

Example:

select nvl2(vector_embedding(all_minilm_l12_v2 using null as data), 'a vector', 'null')
       as embedding_of_null
from   dual;

create table ticket_drafts (
  text       varchar2(4000),
  embedding  vector generated always as
               (vector_embedding(all_minilm_l12_v2 using text as data)) virtual);

Output:

EMBEDDING_OF_NULL
____________________
null

Error starting at line : 5
In command -
create table ticket_drafts (
  text       varchar2(4000),
  embedding  vector generated always as
               (vector_embedding(all_minilm_l12_v2 using text as data)) virtual)
Error report -
ORA-54003: specified data type is not supported for a virtual column

A virtual column would recompute the embedding on every read anyway, the opposite of what you want. Store embeddings in a real column.

Watch the Token Limit

A model reads only so many tokens. all-MiniLM-L12-v2 reads 256, and silently ignores the rest: no error, no warning. This example embeds two pairs of texts that differ only in their last words, after 100 and after 300 words of filler.

Example:

-- two texts that differ only in their last words, after 100 and after 300 words of filler
with filler (words, text) as (
  values (100, rpad('apple ', 600, 'apple ')),
         (300, rpad('apple ', 1800, 'apple ')))
select words as filler_words,
       round(vector_distance(
         vector_embedding(all_minilm_l12_v2
                          using text || 'my invoice was wrong' as data),
         vector_embedding(all_minilm_l12_v2
                          using text || 'the app crashes on start' as data),
         cosine), 4) as distance
from   filler;

Output:

   FILLER_WORDS    DISTANCE
_______________ ___________
            100       0.609
            300           0

After 100 words, different endings give different embeddings. After 300 words, the embeddings are identical: the model never read the endings. A long document embedded whole is represented by its first 200 words or so, and anything it says later cannot be found. Split long documents into chunks that fit the model before embedding them.

Conclusion

VECTOR_EMBEDDING(model USING text AS data) computes an embedding in SQL, so one UPDATE fills a VECTOR column and one ORDER BY VECTOR_DISTANCE runs a semantic search. Keep embeddings current with a row trigger that compares the text, use SELECT ... INTO or DBMS_VECTOR.UTL_TO_EMBEDDING in PL/SQL, store embeddings in real columns, and keep each text within the model's token limit.