NEW Oracle AI Database 26ai · Oracle APEX 26.1

AI Applications with Oracle Database 26ai and APEX 26.1

Vector search, RAG, and AI agents, built where your data already is: in SQL, PL/SQL, and Oracle APEX. Every one of the 237 examples was run, and the book prints the output it really returned.

Buy the paperback Kindle edition Apple Books

Free All 237 examples on GitHubWritten by Vinish Kapoor, Oracle ACE Pro

  • VECTOR type
  • Embeddings
  • Semantic search
  • Hybrid search
  • RAG
  • Natural language to SQL
  • APEX_AI
  • AI agents
The book AI Applications with Oracle Database 26ai and APEX 26.1, in front of two facing pages of the chapter on semantic search
29Chapters
237Examples with real output
3Complete APEX projects
47Figures and screenshots
409Pages
Why this book

AI features, built in the database you know

Most AI tutorials assume Python, a separate vector database, and data copied out of Oracle. Oracle AI Database 26ai and Oracle APEX 26.1 bring vectors, embeddings, and language models to where your data already is. This book shows how, in SQL, PL/SQL, and APEX, with every example run.

The usual way

Python notebooks, and answers you can't check

  • Examples in Python and JavaScript frameworks, far from the tables, security, and transactions of an Oracle application.
  • A separate vector store to keep in sync with the data it describes.
  • Demos that work once, with no word on wrong answers, cost, timeouts, or what to do when the model is busy.
  • New 26ai and APEX 26.1 features, barely described outside the documentation.
With this book

Every AI feature, built in SQL, PL/SQL, and APEX

  • Vectors, embeddings, and vector indexes in the same tables as your data, searched with SQL.
  • 237 examples run in Oracle AI Database 26ai Free with APEX 26.1, each with the output it returned.
  • One sample application, Atlas Support, from the first vector to three finished AI projects.
  • Security, quality, cost, and speed, with the errors you will meet and how to fix them.
How an entry works

Syntax, example, and the output it produced

Every code block in the book carries a label: SYNTAX for how to write a statement, EXAMPLE for code that runs against Atlas Support, and OUTPUT for what it returned when it ran in Oracle AI Database 26ai.

01

Learn the syntax

Each feature starts with its syntax. Here, a similarity search: sort by the distance from a query vector, and keep the first rows.

Syntax
select ... from table order by vector_distance(column, query_vector [, metric]) fetch first k rows only
02

Read the example

A query of Chapter 8 against the knowledge base of Atlas Support: the database embeds the question with its own ONNX model, and finds the nearest articles.

Example · Chapter 8
with q as (select vector_embedding(all_minilm_l12_v2 using 'Our card was charged two times for one invoice' as data) as v from dual) select a.article_id, a.title, round(vector_distance(a.embedding, q.v, cosine), 3) as distance from kb_articles a, q order by distance fetch first 3 rows only;
03

See what it returned

The output, as it ran. The right article comes first, though it shares almost no words with the question: that is search by meaning.

Output, as it ran
ARTICLE_ID TITLE DISTANCE _____________ __________________________________________ ___________ KB-201 Duplicate charges and refunds 0.38 KB-205 Custom fields on invoices 0.575 KB-503 Why Analytics and Billing totals differ 0.654
Examples and screens

The code, and what it did

Every example ran in Oracle AI Database 26ai Free with Oracle APEX 26.1, and every screenshot was taken from the running application. Here are six of them, as they appear in the book.

Atlas Support · Chapter 12 · RAG with citations
PL/SQL · the heart of the function ASK
c_instructions constant varchar2(1000) := 'You are the support assistant of Atlas Software. ' || 'Answer the question using only the numbered sources. ' || 'Cite the sources you used, like [1] or [2]. ' || 'If the sources do not contain the answer, say exactly: ' || 'I could not find this in the Atlas knowledge base.'; l_sources json := retrieve(p_question, p_model); -- the nearest chunks, numbered ... l_answer := generate(l_prompt, p_llm, json_object( 'systemInstruction' value json_object('parts' value json_array( json_object('text' value c_instructions))), 'generationConfig' value json('{"temperature": 0, "thinkingConfig": {"thinkingBudget": 0}}') returning json));-- and then: select ask('Can I get my money back for a duplicate charge?') as answer from dual;
Output
ANSWER ____________________________________________________________________ Yes, Atlas detects most duplicate charges within 24 hours and refunds them automatically, and Support also refunds the duplicate as soon as it is reported [1], [2]. The refund returns the money to your original payment method [2]. Refunds to cards appear on your card statement within 5 to 10 business days, while refunds of direct debits take up to 3 business days [1], [2].
SQL
select a.article_id, a.title, r.score, r.vector_score, r.text_score from json_table( dbms_hybrid_vector.search(json('{ "hybrid_index_name": "KB_ARTICLES_HYBRID", "search_text": "What does the Retry-After header mean?", "return": {"topN": 3, "values": ["rowid", "score", "vector_score", "text_score"]}}')), '$[*]' columns (row_id varchar2(18) path '$.rowid', score number path '$.score', vector_score number path '$.vector_score', text_score number path '$.text_score')) r join kb_articles a on a.rowid = chartorowid(r.row_id);
Output
ARTICLE_ID TITLE SCORE VECTOR_SCORE TEXT_SCORE __________ ____________________________________ ______ ____________ __________ KB-602 API rate limits 55.48 55.93 51 KB-201 Duplicate charges and refunds 53.88 56.67 26 KB-601 Webhooks are disabled after failures 52.89 55.58 26
PL/SQL · the same agent, called from code
-- a conversation with the agent: its prompt, tools, and service come from Shared Components declare l_messages apex_ai.t_chat_messages := apex_ai.c_chat_messages; l_answer clob; begin l_answer := apex_ai.chat(p_agent_static_id => 'atlas-assistant', p_prompt => 'Can I get my money back for a duplicate ' || 'charge?', p_messages => l_messages); dbms_output.put_line('1: ' || l_answer); l_answer := apex_ai.chat(p_agent_static_id => 'atlas-assistant', p_prompt => 'And what is the status of my ticket 15?', p_messages => l_messages); dbms_output.put_line('2: ' || l_answer); end;
The running page
The Ask Atlas assistant answering a question about a duplicate charge and the status of a ticket
PL/SQL · the tool set_ticket_priority
declare l_old tickets.priority%type; begin select priority into l_old from tickets where ticket_id = :TICKET_ID and status not in ('Resolved', 'Closed') for update;update tickets set priority = :PRIORITY where ticket_id = :TICKET_ID; insert into ai_actions (done_by, ticket_id, action, old_value, new_value) values (:APP_USER, :TICKET_ID, 'Priority', l_old, :PRIORITY); apex_ai.set_tool_result( p_result => 'The priority of ticket ' || :TICKET_ID || ' changed from ' || l_old || ' to ' || :PRIORITY || '.', p_notification_message => 'Ticket ' || :TICKET_ID || ' is now ' || :PRIORITY || '.'); exception when no_data_found then apex_ai.set_tool_result( p_result => 'Ticket ' || :TICKET_ID || ' does not exist or is closed. Nothing was changed.'); end;
The running page
The Triage Agent asking the user to confirm a change of priority of ticket 11 to High
PL/SQL · from the function HD_DRAFT_REPLY
-- the instructions: facts only from earlier resolutions, and no promises c_instructions constant varchar2(1000) := 'You draft replies for the support agents of Atlas Software. Write to the customer by ' || 'first name. Use only the facts in the earlier resolutions and the articles. Never ' || 'say that an action was taken; write each step the agent must take in square ' || 'brackets, like [refund issued]. Mention an article by its ID when you refer to it.';-- the resolutions of the three most similar resolved tickets select ... from tickets s where s.ticket_id <> t.ticket_id and s.status in ('Resolved', 'Closed') order by vector_distance(s.embedding, t.embedding, cosine) fetch first 3 rows only;
The running page
The Ticket Workspace with a ticket, similar resolved tickets, and suggested articles
PL/SQL · the function ASK_DATA of Chapter 13
create or replace function ask_data (p_question in varchar2) return json is l_sql clob := generate_sql(p_question); -- the model writes the query l_error varchar2(4000) := check_sql(l_sql); -- one SELECT, nothing else l_rows clob; begin if l_error is null then begin l_rows := atlas_reader.query_json(l_sql); -- runs as a read-only user exception when others then l_error := sqlerrm; end; end if; return json_object('question' value p_question, 'sql' value l_sql, 'rows' value json(l_rows), 'error' value l_error absent on null returning json); end;
The running page
The Ask Your Data page with the tickets created in each month of 2026, as a sentence, a table, and a bar chart

Chapter 12: RAG in PL/SQL. The function retrieves the nearest articles and manuals, numbers them, and asks the model to answer only from them, with citations.

Look inside

Real pages from the book

Chapters that open with what you will learn, code labeled SYNTAX, EXAMPLE, and OUTPUT, figures that explain the ideas, and screenshots of every APEX page at work. Click any page to read it.

ContentsFive parts, 29 chaptersFrom the AI lab to deploying AI applications.
Chapters 1 and 3The ideas, explained for Oracle developersWhat a vector is, what an embedding is, and where each part of an AI application runs.
Chapter 8 · SearchSemantic and hybrid searchSearch by meaning, by keyword, and both at once.
Chapter 12 · RAGAnswers from your own dataRetrieval, prompts, citations, and evaluating the answers.
Chapter 13 · Natural language to SQLQuestions in plain languageGenerated SQL with guard rails, as a read-only user.
Chapter 18 · APEXAI assistants in pagesAgents with a system prompt and tools, in the Show AI Assistant action.
Chapter 19 · APEXAI agents that actTools that change data, confirmation, and an audit trail.
Chapter 21 · APEXSearch pagesVector, Oracle Text, and hybrid search configurations on one page.
Chapter 24 · ProjectThe AI help deskSimilar tickets, suggested articles, and a draft reply to edit.
Chapter 25 · ProjectAsk your dataReporting in plain language, with the SQL shown beside the answer.
Appendix ETroubleshooting AI featuresEvery error the lab met, with its cause, its fix, and its chapter.

Pages shown from the full-color edition. The Kindle and Apple Books editions show code and screenshots in color; the paperback is printed in black and white.

What the book covers

From the first vector to AI in production

The ideas behind AI applications, vector search in the database, generative AI from SQL and PL/SQL, AI in Oracle APEX, three complete projects, and what it takes to run them safely.

CH 1–3FoundationsA free AI lab with Oracle AI Database 26ai, APEX 26.1, and a Gemini key, and the ideas of AI explained for Oracle developers.
CH 4The VECTOR typeDimensions and formats, distances and metrics, and similarity search in SQL and PL/SQL.
CH 5–6EmbeddingsAn ONNX model loaded into the database, and embeddings from a provider such as Gemini.
CH 7–8Indexes and searchHNSW and IVF vector indexes, approximate search, and semantic and hybrid search.
CH 9Loading documentsPDF, Word, and HTML manuals turned into text, split into chunks, and embedded.
CH 10–11Language models from SQLCalling an LLM with DBMS_VECTOR_CHAIN, prompts and settings, summaries, classification, and extraction.
CH 12–13RAG and natural languageAnswers from your own data with citations, and natural language to SQL with guard rails.
CH 14–15Machine learning and graphsClassic machine learning in the database, and vectors with JSON, graphs, and relational data.
CH 16–17Generative AI in APEX 26.1AI services and vector providers, and the APEX_AI package for generation, chat, and tools.
CH 18–22Assistants, agents, and pagesAI assistants and agents, AI in forms and workflows, search pages, and APEX's own AI for developers.
CH 23–25Three projectsA knowledge assistant that finds its own gaps, an AI help desk, and reporting in plain language.
CH 26–29ProductionLocal models with Ollama, security and privacy, quality, cost and speed, and deployment.
Contents

Five parts, 29 chapters

Read Part I to build the free AI lab and learn the ideas, then read on or go to the chapter you need. Six appendices hold the vector packages, APEX_AI, prompt patterns, troubleshooting, and a glossary.

Look inside
IChapters 1–3

Foundations

What the book covers, the free AI lab every example runs in, and the ideas behind AI applications.

  • 1How to Use This Book
  • 2The AI Lab
  • 3AI for Oracle Developers
IIChapters 4–9

Vectors and Search in the Database

The VECTOR type, embeddings, vector indexes, search by meaning, and documents in chunks.

  • 4The VECTOR Type
  • 5Embeddings Inside the Database
  • 6Embeddings from a Provider
  • 7Vector Indexes
  • 8Semantic and Hybrid Search
  • 9Loading Documents
IIIChapters 10–15

Generative AI in SQL and PL/SQL

Language models called from the database, RAG, natural language to SQL, and machine learning.

  • 10Calling an LLM from the Database
  • 11Practical Generation
  • 12RAG in SQL and PL/SQL
  • 13Natural Language to SQL
  • 14Classic Machine Learning in the Database
  • 15Vectors with JSON, Graphs, and Relational Data
IVChapters 16–22

AI in APEX

The AI of Oracle APEX 26.1, from AI services to agents that act, in the Atlas Support application.

  • 16Generative AI in APEX
  • 17The APEX_AI Package
  • 18AI Assistants in Pages
  • 19AI Agents
  • 20AI in Forms and Workflows
  • 21Search Pages in APEX
  • 22APEX's Built-in AI for Developers
VChapters 23–29

Projects and Production

Three complete projects, and what an AI application needs before real users rely on it.

  • 23Project: the Atlas Knowledge Assistant
  • 24Project: the AI Help Desk
  • 25Project: Ask Your Data
  • 26Running AI Locally
  • 27Security and Privacy
  • 28Quality, Cost, and Speed
  • 29Deploying AI Applications
A–FAppendices

The Reference

What to look up while you work.

  • AVector Packages and Functions
  • BAPEX_AI and APEX's AI Components
  • CSwitching Providers
  • DPrompt Patterns
  • ETroubleshooting AI Features
  • FGlossary
Oracle AI Database 26ai and APEX 26.1

AI where your data already is

Oracle AI Database 26ai stores vectors beside your rows, computes embeddings inside the database, and calls language models from SQL. Oracle APEX 26.1 adds AI services, agents, and AI pages on top. The book covers each of them, with the chapter where it is built.

4
The VECTOR type and VECTOR_DISTANCEvectors stored in your tables, compared with cosine, dot, and Euclidean distances, in plain SQL
5
Embeddings inside the databasean ONNX model loaded once, and VECTOR_EMBEDDING in any query, with no call outside
7
HNSW and IVF vector indexesapproximate search that reads only part of the vectors, and the accuracy it trades for speed
8
Hybrid vector indexesDBMS_HYBRID_VECTOR searches by meaning and by keyword at once
10
DBMS_VECTOR_CHAINdocuments to text and chunks, embeddings from a provider, and language models from PL/SQL
17
APEX_AI and AI agentsgeneration, chat, and tools in APEX 26.1, with agents that ask before they act
237examples, each run with its output printed
3complete AI projects in Oracle APEX 26.1
44errors and surprises in Appendix E, with their fixes
50terms of AI explained in the glossary

Run in 26ai

Every example ran in Oracle AI Database 26ai Free, release 23.26, with Oracle APEX 26.1.

Real output

No answer was written by hand: the book prints what each query and each model returned.

One application

All examples belong to Atlas Support, the help desk of a software company, with its tickets and knowledge base.

A free lab

Oracle AI Database 26ai Free and APEX cost nothing, Chapter 2 shows how to get a free Gemini key, and Chapter 26 runs a model locally.

Vinish Kapoor

Oracle ACE Pro · Oracle developer and architect

  • Oracle ACE every year since 2020
  • Oracle SQL, PL/SQL, and APEX for more than twenty years
  • Author of books on Oracle APEX, Oracle Database, and Oracle Forms
vinish.dev@vinish_kapooru/vinishkapoor
About the author

Two decades of Oracle, now with AI

Oracle applications since the early 2000s

Vinish wrote his first programs in 2001 and moved to Oracle Forms, SQL, and PL/SQL soon after. He has designed and built database applications for healthcare, education, hospitality, logistics, legal services, finance, and retail, first with Oracle Forms and today with Oracle APEX.

A developer and architect

He builds products of his own, such as the Rounds hospital management system and VinAura PDF, a visual report designer for Oracle APEX, and his articles have appeared in AI News, Developer Tech, HackerNoon, and DEV Community.

His fifth book

His earlier books are Oracle APEX 26.1: The Complete Guide, Oracle APEX 26.1 API by Example, Oracle Database 26ai SQL and PL/SQL, and Oracle Forms 14c. His blog, vinish.dev, formerly foxinfotech, is read by developers around the world.

Vectors. Embeddings. RAG. AI agents. Each one built in SQL, PL/SQL, and APEX, and shown with the output it really returned.

Get the book
  • Paperback, 409 pages
  • Kindle and Apple Books
  • Free code on GitHub
FAQ

Frequently asked

Anything else? Ask on vinish.dev.

Who is this book for?

Oracle developers who know SQL and PL/SQL and want to add AI features to their applications: PL/SQL developers, APEX developers, and architects who must decide what AI can do for their systems. No background in machine learning or Python is needed; Chapter 3 explains the ideas from the beginning.

Which versions does it cover?

Oracle AI Database 26ai and Oracle APEX 26.1. Every example ran in Oracle AI Database 26ai Free, release 23.26, with APEX 26.1, and the book marks what needs a newer release or another edition, such as Select AI of Autonomous Database, which Chapter 13 builds in PL/SQL instead.

Were the examples really run?

Yes. All 237 examples ran in the lab of Chapter 2, and the book prints the output each one returned, including the answers of the language models. Model answers differ from run to run, so yours will say the same things in other words.

Which AI models does it use?

An ONNX embedding model (all-MiniLM-L12-v2) runs inside the database. Google Gemini answers questions and writes text, through DBMS_VECTOR_CHAIN and the AI services of APEX. Chapter 26 runs a model locally with Ollama, and Appendix C shows how to switch to OpenAI, Cohere, Mistral, Anthropic, and other providers.

Do I need to pay for anything?

No. Oracle AI Database 26ai Free and Oracle APEX are free, and a Gemini key on the free tier is enough for every example of the book; Chapter 2 shows the setup. A key on a paid project raises the limits and keeps your prompts out of Google's training, and Chapter 28 shows what the calls cost.

What is Atlas Support?

The book's sample application: the help desk of a software company, with products, customers, agents, 400 tickets, a knowledge base of articles, and a library of PDF and Word manuals. Every example uses it, and Part V builds three AI projects on it in Oracle APEX.

Does it cover security and cost?

Yes. Chapter 27 covers credentials, network access, redaction of personal data, prompt injection, and row-level security for what an assistant retrieves. Chapter 28 measures answer quality, tokens, cost, and speed, and Chapter 29 deploys the application.

Where is the code?

On GitHub, free: github.com/devvinish/oracle-ai-book-code has all 237 examples with their output, the scripts that install the Atlas Support schema and its documents, and the finished APEX application.

Is the paperback in color?

The paperback is printed in black and white on white paper, 7.5 by 9.25 inches, 409 pages, with a glossy cover. The Kindle and Apple Books editions show the code, figures, and screenshots in color.

Get the book

Paperback, Kindle, or Apple Books

Every edition has the same 29 chapters, six appendices, and index. Pick the paperback for your desk, and the Kindle or Apple Books edition for code and screenshots in color on any screen.

  1. Get the bookFrom Amazon or Apple Books, in the edition you prefer.
  2. Build the AI labOracle AI Database 26ai Free, APEX 26.1, and a Gemini key, as Chapter 2 describes.
  3. Run the examplesInstall Atlas Support from GitHub, and run every example as you read.

Paperback

$39.99on Amazon.com

  • 409 pages, 7.5 × 9.25 in
  • Black and white on white paper
  • Glossy cover
  • ISBN 9798178836798
Buy the paperback

Kindle edition

$12.99on Amazon.com

  • Code and screenshots in color
  • Reflowable on any Kindle
  • Linked contents and index
  • Kindle apps for phone, tablet and PC
Buy for Kindle

Apple Books

Code and screenshots in color, on iPhone, iPad, and Mac.

$12.99on Apple Books

Buy on Apple Books
github.comdevvinish / oracle-ai-book-code
Public

The code of the book, free: every example with its output, the Atlas Support schema and documents, and the finished APEX application.

  • examples/the 237 examples and their output, one folder per chapter
  • setup/atlas/the Atlas Support schema, its data, and its documents
  • apex/the finished Atlas Support application
Open on GitHub
Or clone it
git clone https://github.com/devvinish/oracle-ai-book-code.git
00