If you know SQL and PL/SQL, you already have most of what it takes to build AI features in Oracle. What is new is a handful of ideas: what a language model does, why its answers change, what an embedding is, and how vectors turn "similar meaning" into a number you can sort by.
This guide explains those ideas in database terms, with three small examples you can run in Oracle AI Database 26ai: a model answering the same prompt twice, an embedding, and five sentences compared by meaning.
Code for This Guide
The examples are in the examples/ch03 folder of the Oracle AI code repository on GitHub, each with the output it produced.
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 call Google Gemini through a database credential named GEMINI_CRED. Setting that up is covered in how to call Gemini from Oracle Database with a stored credential.
Two Kinds of AI in an Oracle Application
| Kind | What happens | Examples |
|---|---|---|
| Generation | A language model reads text and writes text. | Answers, summaries, classifications, draft replies |
| Meaning as data | A model turns text into a vector, and the database compares vectors. | Semantic search, similar records, recommendations |
Most useful features combine the two.
What a Language Model Does
A large language model (LLM) is trained on a huge amount of text to do one thing: continue a text with the most plausible next words. Asked a question, the most plausible continuation is an answer. Given a ticket and an instruction to classify it, it is a category. Answering, summarizing, translating, extracting fields, and writing SQL are all that one skill, steered by the text you send.
That text is the prompt, and your code assembles it: an instruction, the data the model needs, and the question. The model's reply is the completion. From the database, DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT sends a prompt and returns the completion as a CLOB.
The Same Prompt Gives Different Answers
A model does not look an answer up. It writes one, choosing each word with some randomness, so the same prompt sent twice gets two answers.
Example:
-- the same prompt, sent twice
select dbms_vector_chain.utl_to_generate_text(
'Suggest a short subject line for a ticket about a duplicate credit card charge.',
json('{"provider": "googleai", "credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-flash-latest:generateContent"}')) as first_answer
from dual;
select dbms_vector_chain.utl_to_generate_text(
'Suggest a short subject line for a ticket about a duplicate credit card charge.',
json('{"provider": "googleai", "credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-flash-latest:generateContent"}')) as second_answer
from dual;Output:
FIRST_ANSWER _________________________________________________________ Here is a direct and effective option: **Duplicate Credit Card Charge** *Alternatives depending on your details:* * **Billing Issue: Charged Twice for Order #[Number]** * **Request for Refund: Duplicate Charge** SECOND_ANSWER _____________________________________________________________________________________________ Here are a few short options, depending on your needs: * **Billing Issue: Duplicate Charge** *(Best overall)* * **Duplicate Credit Card Charge** *(Direct and simple)* * **Double Charged: Order #[Insert Order Number]** *(Best if you have an order reference)*
Three lessons hide in that output:
- The two answers say the same thing in different words, so you cannot test an AI feature by comparing its answer with an expected string. Test that answers contain the facts they must, and nothing they must not.
- The answers are formatted in Markdown, with ** and * marks, because models are trained for chat windows. If your page shows plain text, the prompt must ask for plain text.
- The model gave several options when one was wanted. Prompts that state exactly what to return, and in what form, are the first skill of AI development.
Tokens and the Context Window
Models read tokens, not characters or words. A token is a piece of a word, about four characters of English on average. Tokens matter in three ways:
| Where | Why tokens matter |
|---|---|
| Limits | A model accepts a maximum number of tokens per request, its context window, counting the prompt and the answer together. Current Gemini models accept about a million. |
| Cost | Paid AI is priced per million tokens, with input and output priced separately. |
| Chunking | Embedding models read far fewer tokens, often a few hundred, so long documents are split into chunks before they are embedded. |
More on tokens is in what are tokens in AI models.
What a Model Does Not Know
An LLM knows what was in its training text, up to its training date, and nothing else. It does not know your products, prices, or tickets. Asked about them, it still writes the most plausible continuation, and a plausible answer about facts it does not have is an invented one: a hallucination.
The cure is not a bigger model. It is giving the model the facts, in the prompt. That is retrieval-augmented generation, described below.
What a Vector Is
A vector is a list of numbers in a fixed order, such as [0.10, 0.95]. Each position is a dimension. In AI, a vector describes a piece of data, and its use is comparison: two lists of numbers can be measured against each other in a way two paragraphs of text cannot.
Picture the simplest case. Score each help desk article with two numbers between 0 and 1: how much it is about account problems, and how much about billing. The article on duplicate charges gets [0.10, 0.95], the one on sign-in problems [0.90, 0.05]. Two numbers are a point on a graph.
Articles about the same subject sit close together. Score a question the same way, and "Why was I charged twice?" lands among the billing articles. The nearest points are the answers. That is the whole mechanism of AI search: turn everything into vectors, and find the nearest ones.
Real systems change three things, but not the idea:
- Many more dimensions: hundreds or thousands, such as 384 for a small in-database model or 3,072 for Gemini's embedding model.
- No person chooses the numbers. A model computes them, and only the distances between them mean anything.
- Any data can become a vector: text, images, audio, or rows of a table.
What an Embedding Is
An embedding model reads text and returns a vector of fixed size, such that texts with similar meanings get vectors that are close together. That vector is the embedding of the text.
Example:
with e as (
select 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 v
from dual
)
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 e;Output:
DIMENSIONS FORMAT LENGTH FIRST_NUMBERS
_____________ __________ _________ __________________________________________________
3072 FLOAT32 1 [3.0464923E-002,1.47858886E-002,-1.10975225E...The embedding has 3,072 dimensions in the FLOAT32 format, and a length of 1: Gemini returns normalized vectors. The numbers mean nothing to a person. What matters is how far they are from another sentence's numbers. How Oracle stores such vectors is covered in how to use the VECTOR data type in Oracle AI Database 26ai.
Distance Measures Meaning
The next example embeds five sentences and measures the cosine distance of each from "I cannot sign in to my account" and from "We were billed twice this month". Smaller means closer.
Example:
create table sentences (id number, text varchar2(200), v vector);
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 = dbms_vector_chain.utl_to_embedding(text,
json('{"provider": "googleai", "credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-embedding-001"}'));
commit;
-- every sentence against sentence 1 and sentence 3
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.452
2 Login keeps saying invalid credentials 0.327 0.465
3 We were billed twice this month 0.452 0
4 Our card shows two identical payments 0.473 0.199
5 The mobile app closes right after it opens 0.414 0.456In the FROM_BILLED_TWICE column, "Our card shows two identical payments" is by far the nearest, at 0.199, though it shares no words with "billed twice". In FROM_SIGN_IN, "Login keeps saying invalid credentials" is the nearest, again with no word in common. A keyword search for "billed" would have missed "two identical payments" completely. Comparing meaning instead of words is semantic search.
Distances are relative to the model. A distance of 0.2 means very similar for this model, while another model might give the same pair 0.05 or 0.4. Compare distances from the same model, and judge them by their order and the gaps between them rather than by fixed thresholds, until you have measured thresholds on your own data.
Where Embeddings Come From
| Where | Strengths | Costs |
|---|---|---|
| An AI provider, such as Gemini or OpenAI | Strong models with many dimensions | Every text is sent out, and every embedding is a paid request. |
| Inside the database, an ONNX model run by VECTOR_EMBEDDING | Nothing leaves the database, no cost per request, a million rows in one statement | Smaller models with fewer dimensions, usually enough |
Embedding inside the database is shown in how to generate embeddings in SQL with VECTOR_EMBEDDING. Whichever you choose, embed the data and the questions with the same model. Embeddings from different models cannot be compared, and usually do not even have the same number of dimensions.
Retrieval-Augmented Generation
Retrieval-augmented generation (RAG) combines both kinds of AI to answer questions from your own data. Instead of asking a model something it cannot know, the application finds the relevant information first and asks the model to answer from it.
- Question: a user asks "Why was I charged twice?"
- Embed: the application embeds the question with the model that embedded the knowledge base.
- Retrieve: a similarity search, ORDER BY VECTOR_DISTANCE, finds the nearest chunks of the knowledge base.
- Augment: the application builds the prompt from an instruction ("answer only from the articles below"), the retrieved text, and the question.
- Generate: the model answers from the articles, and the application shows the answer with links to its sources.
RAG fixes hallucination where it matters, because the model answers from facts you supplied and you can show which. The knowledge also stays in your database, where SQL can update, secure, and search it.
Agents
A chat answers; an agent acts. An AI agent is a language model that can call tools: functions your application provides, such as "look up a ticket" or "set a ticket's priority". Given a request, the model decides which tools to call and with what arguments, the application runs each call and returns the result, and the model continues until the request is done.
Because the model chooses the actions, every tool must check permissions as if a user had called it, and anything that changes data should ask for confirmation first.
AI Features of Oracle AI Database 26ai
Oracle AI Database 26ai, the successor of Oracle Database 23ai, builds these ideas into SQL and PL/SQL. They are part of the database, run by the same SQL engine, and protected by the same privileges as your tables.
| Feature | What it does |
|---|---|
| VECTOR data type | Stores vectors in columns, like any other data |
| VECTOR_DISTANCE | Measures similarity, so ORDER BY finds the nearest rows, with joins and filters as usual |
| Vector indexes (HNSW, IVF) | Make similarity search fast on millions of rows |
| Embedding models in the database | Run an ONNX model with VECTOR_EMBEDDING inside a query |
| DBMS_VECTOR_CHAIN | Turns documents into text and chunks, gets embeddings from providers, and calls language models |
| Hybrid vector indexes | Combine Oracle Text keyword search with vector search in one index |
| In-database machine learning | Trains and scores classification, regression, and clustering models in SQL |
| Vectors with JSON and graphs | Vector search over JSON documents, duality views, and property graphs |
A language model itself does not run in the database: it runs at a provider, on hardware built for it. The database acts as the client. A PL/SQL call reads the provider's key from a credential, sends the prompt over HTTPS, and returns the answer as a CLOB. From then on, the answer is data you can store, join, filter, and show in APEX.
What Runs Where
| Layer | Does |
|---|---|
| Oracle APEX | Pages, forms, and reports; AI assistants, agents, and Generative AI services |
| Oracle AI Database 26ai | The data, vectors and vector indexes, in-database embedding models, search, document chunking, machine learning, and credentials |
| The AI provider | The language model, and provider embeddings when you choose them |
The line between the database and the provider is the line your data crosses. Embedding and searching inside the database, and sending the provider only the few chunks a question needs, keeps most of your data where it is.
For a broader, product-neutral view of vector stores, see vector databases explained simply.
Conclusion
A language model continues text, so prompts steer it, its answers vary, and it invents what it does not know. An embedding turns text into a vector, and similar meanings get nearby vectors whatever words they use, so search becomes ORDER BY a distance. RAG joins the two: retrieve the facts from your data, then let the model answer from them. Agents add tools, which must enforce permissions. Oracle AI Database 26ai builds vectors, embeddings, search, and model calls into SQL, so AI answers come back into tables like any other data.
