In an earlier post I worked through vector distance using numbers I typed by hand. That covers the mechanics, but it leaves the interesting half untouched: where do real vectors come from, and does semantic search actually work on text?

This post answers both. The model runs inside the database. No network calls, no cloud credentials, no external service — which turned out to matter more than I expected.

Why local, and not a hosted service

My first attempt used OCI Generative AI. The setup is real work: an API signing key, a DBMS_VECTOR credential holding five separate values, and a network ACL granting the database permission to make outbound HTTPS calls. I got all of it working — the database completed an authenticated round trip to Oracle’s inference endpoint and got a response back.

Then every model call returned Entity with key cohere.embed-multilingual-v3.0 not found. The console’s own playground failed the same way on a different Cohere model, while Google and xAI chat models worked fine. Cohere simply wasn’t provisioned in that tenancy, and Cohere was the only embedding vendor offered in my region. Nothing I could fix from SQL.

That detour was worth something, though, and I’ll come back to it at the end — the ACL debugging taught me a diagnostic I hadn’t seen documented anywhere.

But the local route is better regardless. The model file lives in the database, embeddings are generated in-process, and no data leaves the server. For anyone whose customers won’t send text to a third-party API — which is most regulated industries — this is the only viable option anyway.

Getting the model

Oracle publishes pre-converted ONNX models ready to load. The list is in the Oracle Machine Learning documentation under “Import Pretrained Models in ONNX Format.”

For semantic similarity, all_MiniLM_L12_v2 is the right starting point: 384 dimensions, about 117 MB compressed. The others are larger or serve different purposes — multilingual_e5_base is a gigabyte and aimed at non-English text, and the CLIP models match images to text.

Take the augmented build. Oracle bakes the required metadata into the file, which means the load call needs no metadata argument at all.

sudo su - oracle
mkdir -p /home/oracle/vecdump
cd /home/oracle/vecdump
curl -o all_MiniLM_L12_v2_augmented.zip "<PAR URL from the docs table>"
unzip all_MiniLM_L12_v2_augmented.zip
ls -lh

-rw-rw-rw-. 1 oracle oinstall 128M  all_MiniLM_L12_v2.onnx
-rw-rw-rw-. 1 oracle oinstall  12K  LICENSE_ATTRIBUTION.txt
-rw-rw-rw-. 1 oracle oinstall 4.2K  README-ALL_MINILM_L12_V2-augmented.txt

128 MB unzipped. Note the exact .onnx filename — you need it for the load.

Privileges and the directory object

ONNX models are stored as mining models, so the loading user needs CREATE MINING MODEL. It also needs read access to a directory object pointing at wherever the file sits.

As sysdba, inside the PDB:

sql

alter session set container = FREEPDB1;
grant create mining model to vector;
create or replace directory VEC_DUMP as '/home/oracle/vecdump';
grant read, write on directory VEC_DUMP to vector;

A note that saved me time later: db_developer_role does not include CREATE MINING MODEL, and it doesn’t include CREATE CREDENTIAL either. Both have to be granted separately, and the errors when they’re missing (ORA-27486, insufficient privileges) don’t name the privilege you’re short of.

Loading it

sql

begin
  dbms_vector.load_onnx_model(
    directory  => 'VEC_DUMP',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'MINILM_L12');
end;
/

That’s the whole thing for an augmented model. If you use a model you converted yourself, you’d add a metadata argument describing the function and input mapping.

Takes a minute or two for 128 MB. Verify:

sql

select model_name, mining_function, algorithm,
       round(model_size/1024/1024) as size_mb
from user_mining_models;

MODEL_NAME    MINING_FUNCTION   ALGORITHM   SIZE_MB
MINILM_L12    EMBEDDING         ONNX        127

The model is now a database object. It backs up with the database, it’s available to any session, and it needs nothing external at runtime.

First embedding

sql

select vector_embedding(MINILM_L12 using 'oracle rac node eviction' as data) as v
from dual;

[-6.94518536E-002,9.12421197E-003,-5.45352362E-002,-3.43639106E-002,...

384 floats from four English words.

The syntax deserves a note because it looks wrong the first time. The model name is unquoted and there is no comma before using. It reads like a keyword construct rather than a function call, which is exactly what it is.

This is also the point where the distinction from my earlier post becomes concrete. VECTOR('[4,3]') is a type conversion — text that already looks like a vector, turned into the VECTOR type, no intelligence involved. VECTOR_EMBEDDING() runs actual language through a neural network. Two completely different operations that beginners (me, a week ago) tend to blur together.

The demonstration

Here’s the part that makes the concept click. A small table of the kind of text I deal with daily, plus one deliberate outlier:

sql

drop table kb purge;
create table kb (
  id        number generated always as identity primary key,
  content   varchar2(400),
  embedding vector(384, float32)
);
insert into kb (content) values ('CSSD terminated the node because network heartbeat was missed for 30 seconds');
insert into kb (content) values ('ASM disk group went offline after storage array lost connectivity');
insert into kb (content) values ('LMHB detected an LMS process hang and evicted the instance with ORA-29770');
insert into kb (content) values ('Listener refused connection because the service was not registered');
insert into kb (content) values ('Recipe for chocolate cake with buttercream frosting');
insert into kb (content) values ('Interconnect packet loss caused excessive gc block transfer waits');
commit;
update kb set embedding = vector_embedding(MINILM_L12 using content as data);
commit;

One UPDATE embeds every row. Now search using words that appear nowhere in the table:

sql

set linesize 200
column content format a70
select content,
       round(vector_distance(embedding,
             vector_embedding(MINILM_L12 using 'node got kicked out of the cluster' as data),
             cosine), 4) as dist
from kb
order by dist
fetch first 3 rows only;

CONTENT                                                                  DIST
---------------------------------------------------------------------- ------
CSSD terminated the node because network heartbeat was missed for 30 s  .4716
econds
ASM disk group went offline after storage array lost connectivity       .7104
LMHB detected an LMS process hang and evicted the instance with ORA-29  .7244
770

The query said “kicked out of the cluster.” The winning row says “CSSD terminated the node because network heartbeat was missed.” The only word they share is “node.” A LIKE '%kicked%' search returns nothing at all.

Two more:

sql

-- 'storage went away'
ASM disk group went offline after storage array lost connectivity        .5223
LMHB detected an LMS process hang and evicted the instance with ORA-29   .7882
Interconnect packet loss caused excessive gc block transfer waits        .8403
-- 'slow block transfers between nodes'
Interconnect packet loss caused excessive gc block transfer waits        .4519
CSSD terminated the node because network heartbeat was missed for 30 s   .7365
LMHB detected an LMS process hang and evicted the instance with ORA-29   .8731

Each found its intended row, and in each case there’s a visible gap between the winner and the rest.

The failure mode nobody shows you

Now ask something the table has no answer for:

sql

select content,
       round(vector_distance(embedding,
             vector_embedding(MINILM_L12 using 'how do I file my taxes' as data),
             cosine), 4) as dist
from kb
order by dist
fetch first 3 rows only;

Recipe for chocolate cake with buttercream frosting                     .9597
ASM disk group went offline after storage array lost connectivity      1.0227
Interconnect packet loss caused excessive gc block transfer waits      1.0614

Three rows came back. There is no “no results found” in vector search — you asked for the top three and you got the top three, regardless of whether anything was relevant.

But look at the numbers against the earlier queries. Genuine matches landed at 0.45 to 0.52. This noise sits at 0.96 and above, two results past 1.0. The distances are telling you plainly that nothing here is close.

That gap is the threshold, and applying one is the whole difference between a retrieval system that helps and one that confidently hands back a cake recipe because it was the least-bad option available. Every production RAG pipeline needs a distance cutoff; the ranking alone is not enough.

(Incidentally, the cake won because both it and the tax question are everyday-life topics while everything else is database infrastructure. The model isn’t malfunctioning — it’s answering a question that has no good answer.)

The ACL problem, for anyone who goes the hosted route

If you do use OCI Generative AI, the database needs an ACL granting it outbound network access. DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE creates that entry — Oracle blocks PL/SQL network calls by default, which is why every RAG tutorial includes this step.

I lost an hour to it, and the cause is worth documenting because I couldn’t find it written down anywhere.

The symptom was ORA-24247 on a host where the ACE looked perfect:

sql

select acl, principal, privilege, is_grant from dba_network_acl_privileges;

Correct host, principal VECTOR, both connect and resolve, is_grant true, no date restrictions, right container. And yet the user couldn’t connect, and this returned nothing:

sql

select * from user_network_acl_privileges;   -- no rows selected

The answer is in a column the usual views don’t show:

sql

select host, principal, principal_type, privilege from dba_host_aces;

HOST                                    PRINCIPAL   PRINCIPAL_TYPE
inference.generativeai...oraclecloud.com  VECTOR    APPLICATION     <-- wrong

APPLICATION, not DATABASE. An ACE can name either a database user or a Real Application Security application principal, and principal_type decides which. Mine had been created for an XS application principal named VECTOR — an object that doesn’t exist. Oracle stores the ACE without validating the principal, so the DBA views display a rule that can never match anything.

The cause was passing a literal 1 for principal_type. The correct value is the constant XS_ACL.PTYPE_DB. Note the naming: the type is XS$ACE_TYPE with a dollar sign, but the package holding the constants is XS_ACL without one. Mixing those up is easy and the resulting error (PLS-00302: component 'PTYPE_DB' must be declared) tempts you into substituting a number.

sql

begin
  dbms_network_acl_admin.append_host_ace(
    host => '<endpoint>',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','resolve'),
              principal_name => 'VECTOR',
              principal_type => xs_acl.ptype_db));
end;
/

After that, USER_NETWORK_ACL_PRIVILEGES populated and the call went through.

Two debugging techniques came out of this that I’ll keep. First:

sql

utl_http.set_detailed_excp_support(true);

Without it, everything collapses into a useless generic ORA-29273: HTTP request failed. With it, you get the actual error — ORA-24247 for ACL, ORA-29024 for certificate validation, and so on.

Second, test the same endpoint with curl from the host. If curl succeeds and the database fails, the network is fine and the problem is inside the database — ACL or wallet. Curl uses the OS certificate bundle while the database uses its own, so they genuinely can disagree.

Where this goes next

The pipeline is complete: a local model, real embeddings, working semantic search, and a sense of when the results mean anything.

What’s left to build on top is chunking — real documents are longer than one sentence, so they get split into passages before embedding — vector indexes once the row count makes full scans impractical, and feeding the retrieved passages to a language model, which is the step that turns retrieval into RAG.

Worth being clear about what this does and doesn’t do, because it’s easy to oversell. Vector search finds documents whose meaning is close to your question. It does not diagnose anything. Point it at “node eviction” and it surfaces your notes about node eviction — it does not read your cluster’s logs and tell you why node 3 went down at 2am. The reasoning step is separate, and it only works on what you fed into the index in the first place.

That last constraint is the one I’d keep in mind. A retrieval system knows only what’s in it, and when a failure mode isn’t represented, it returns the nearest thing it has with no indication that it’s wrong. Which is exactly what the tax query demonstrated.