NEW Written for Oracle AI Database 26ai · SQL and PL/SQL

Oracle Database 26ai SQL and PL/SQL Book

Every SQL function, statement, and developer package of Oracle AI Database 26ai — each with an example that ran in Oracle AI Database 26ai Free and the output it really produced. The complete developer's guide, from your first SELECT to nearly 100 built-in packages.

Buy the paperback Kindle edition

Free 677 examples on GitHubWritten by Vinish Kapoor, Oracle ACE Pro

  • Analytic functions
  • JSON
  • AI Vector Search
  • Property graphs
  • PL/SQL
  • DBMS_SQL
  • DBMS_SCHEDULER
  • UTL_HTTP
The book Oracle Database 26ai SQL and PL/SQL, in front of two facing pages of Chapter 22 with FUZZY_MATCH and its output
66Chapters
677Runnable examples
578Reference entries
14Tables of Nimbus Air
700Pages
Why this book

The documentation, with the output it never shows

Oracle's manuals tell you the syntax. They rarely show what a statement returns, on data that looks like a real application. This book runs every example on Oracle AI Database 26ai and prints what came back.

The usual way

Syntax diagrams, and results left to you

  • A dozen reference manuals: SQL, PL/SQL, packages and types, JSON, XML, vector search.
  • Examples on EMP and DEPT, and hardly ever the output they produce.
  • Blog snippets written for 11g, 12c, or 19c, with no word on what has changed.
  • Features new since 19c that you may not know exist.
With this book

Every feature, run and shown

  • One book for SQL, PL/SQL, and the developer packages of Oracle AI Database 26ai.
  • 677 examples, printed with the output they produced — none of it typed by hand.
  • One realistic airline schema, Nimbus Air, from the first chapter to the last.
  • What is new since 19c, marked in the text and collected in Appendix B.
How an entry works

Syntax, example, real output

Every function, statement, and package subprogram gets the same treatment: what it is for, how to write it, an example on the Nimbus Air data, and exactly what that example returned.

01

Find the feature

By task in the contents, or by name in the index. Each entry starts with its purpose and its syntax — here, a data quality operator new in 26ai.

Chapter 22 · the syntax
fuzzy_match(algorithm, string1, string2 [, unscaled] [, relate_to_shorter] [, edit_tolerance n])
02

Run the example

A short, practical query on the book's airline schema: which customers are called something like Mueller?

Chapter 22 · the example
select c.first_name || ' ' || c.last_name as customer, fuzzy_match(levenshtein, c.last_name, 'Mueller') as levenshtein, fuzzy_match(jaro_winkler, c.last_name, 'Mueller') as jaro_winkler, fuzzy_match(trigram, c.last_name, 'Mueller') as trigram, fuzzy_match(levenshtein, c.last_name, 'Mueller', unscaled) as edits from customers c where fuzzy_match(jaro_winkler, c.last_name, 'Mueller') >= 70 order by jaro_winkler desc;
03

Read the real output

What the query returned in Oracle AI Database 26ai: Müller is two edits from Mueller, but scores 80 of 100 by Jaro-Winkler.

Output, as it ran
CUSTOMER LEVENSHTEIN JARO_WINKLER TRIGRAM EDITS ______________ ______________ _______________ __________ ________ Neha Müller 72 80 20 2
Examples and output

677 examples, each with what it returned

A runner executed every example with SQLcl on Oracle AI Database 26ai Free, and the book prints exactly what came back. Here are six of them, as they appear in the book.

SQLcl · Chapter 15 · QUALIFY
SQL
-- the best-paid employee of each department, without a subquery select department_id, last_name, salary from employees qualify row_number() over (partition by department_id order by salary desc) = 1 order by department_id;
Output
DEPARTMENT_ID LAST_NAME SALARY ________________ ____________ _________ 10 Haddad 48000 20 Clarke 27000 30 Rahman 22000 40 Evans 34000 50 Mehta 33000 60 Okafor 29000 70 Fernández 23000 80 Smith 15800 90 Chen 12000 100 Nguyen 2150010 rows selected.
SQL
-- runs of consecutive delayed departures (15 minutes or more) on route 1 select * from (select flight_no, scheduled_departure as sched, extract(hour from (actual_departure - scheduled_departure)) * 60 + extract(minute from (actual_departure - scheduled_departure)) as delay from flights where route_id = 1 and status = 'ARRIVED') match_recognize ( order by sched measures first(late.sched) as run_start, count(late.*) as flights, max(late.delay) as worst_delay one row per match pattern (late{2,}) define late as late.delay >= 15 );
Output
RUN_START FLIGHTS WORST_DELAY __________________________________ __________ ______________ 02-JAN-2026 02:05:00 ASIA/DUBAI 3 74 23-JAN-2026 02:05:00 ASIA/DUBAI 2 34 01-FEB-2026 02:05:00 ASIA/DUBAI 3 54 08-FEB-2026 02:05:00 ASIA/DUBAI 2 82
SQL
-- from Auckland to London with exactly one change select * from graph_table (nimbus_network match (a is airport) -[r1 is route]-> (via is airport) -[r2 is route]-> (b is airport) where a.airport_code = 'AKL' and b.airport_code = 'LHR' columns (via.airport_code as change_at, r1.distance_km + r2.distance_km as total_km) ) order by total_km;
Output
CHANGE_AT TOTAL_KM ____________ ___________ DXB 19697
SQL
select c.customer_id, j.* from customers c, json_table(c.loyalty, '$' columns (member_id varchar2(10) path '$.memberId', tier varchar2(10) path '$.tier', points number path '$.points', seat varchar2(10) path '$.preferences.seat', nested path '$.favoriteAirports[*]' columns (fav_no for ordinality, airport varchar2(3) path '$'))) j where c.customer_id in (1, 2);
Output
CUSTOMER_ID MEMBER_ID TIER POINTS SEAT FAV_NO AIRPORT ______________ ____________ _______ _________ ________ _________ __________ 1 NM100037 Blue 3296 aisle 1 CDG 1 NM100037 Blue 3296 aisle 2 JNB 1 NM100037 Blue 3296 aisle 3 NBO 2 NM100074 Blue 7823 any 1 SYD
SQL
-- every sentence of every review, with its embedding create table review_sentences as select sentence, vector_embedding(all_minilm_l12_v2 using sentence as data) as embedding from (select distinct c.chunk_text as sentence from reviews r, vector_chunks(r.review_text by words max 16 split by sentence) c);-- the sentences closest in meaning to a question, whatever words they use select sentence, round(vector_distance(embedding, vector_embedding(all_minilm_l12_v2 using 'The internet connection failed' as data), cosine), 3) as distance from review_sentences order by distance fetch first 2 rows only;
Output
Table REVIEW_SENTENCES created.SENTENCE DISTANCE ____________________________________________________________________ ___________ Wi-Fi did not work for the whole flight. 0.585 The flight was delayed by over two hours with little information. 0.732
PL/SQL
declare type t_crew is table of crew_assignments%rowtype; v_crew t_crew; v_start timestamp; function ms_since (p_start timestamp) return number is begin return round(extract(second from (localtimestamp - p_start)) * 1000); end; begin select * bulk collect into v_crew from crew_assignments;v_start := localtimestamp; for i in 1 .. v_crew.count loop insert into crew_copy values v_crew(i); -- one statement per row end loop; dbms_output.put_line(v_crew.count || ' rows, row by row: ' || ms_since(v_start) || ' ms'); rollback;v_start := localtimestamp; forall i in 1 .. v_crew.count insert into crew_copy values v_crew(i); -- one statement for all rows dbms_output.put_line(v_crew.count || ' rows, FORALL: ' || ms_since(v_start) || ' ms'); rollback; end; /
Output
11224 rows, row by row: 54 ms 11224 rows, FORALL: 3 msPL/SQL procedure successfully completed.

Chapter 15: QUALIFY filters on an analytic function, so the best-paid employee of each department takes no subquery.

Look inside

Real pages from the book

Syntax blocks, examples on the Nimbus Air data, and the output of every one of them, with chapters that open with what you will learn. Click any page to read it.

ContentsEight parts, 66 chaptersFrom installing the database to the built-in packages.
Chapters 1 and 22The layout of an entryPurpose, syntax, an example, and its real output — for every feature.
Chapter 5 · Sample schemaNimbus Air, a real airlineFourteen tables of airports, routes, flights, crew, bookings, and reviews.
Chapter 16 · QueriesPatterns in ordered rowsMATCH_RECOGNIZE, PIVOT, and MODEL, each on real questions.
Chapter 19 · JSONDuality viewsRelational rows as JSON documents, readable and updatable.
Chapter 21 · AI Vector SearchSearch by meaningEmbeddings, vector distance, and semantic search, all in SQL.
Appendix BWhat is new since 19c45 features for developers, each with its chapter.
IndexFind anything by nameFunctions, statements, packages, and their subprograms, with their pages.

Pages shown from the full-color edition. The Kindle edition shows code in color; the paperback is printed in black and white.

What the book covers

SQL, PL/SQL, and the packages, completely

Every SQL function family, every kind of query, DML and DDL, the PL/SQL language from blocks to SQL macros, and the built-in packages that developers use.

CH 2–5Getting startedOracle AI Database 26ai Free in a container, SQL*Plus, SQLcl, SQL Developer, and the Nimbus Air schema.
CH 6–8Types and expressionsBOOLEAN, VECTOR, and JSON types, literals, operators, conditions, pseudocolumns, and format models.
CH 9–14QueriesSELECT, joins, GROUP BY ALL, subqueries, set operators, WITH, recursive and hierarchical queries.
CH 15–17Analytics and patternsWindows and QUALIFY, PIVOT, MATCH_RECOGNIZE, MODEL, flashback queries, and sampling.
CH 18–21Graphs, JSON, vectors 26aiSQL property graphs, JSON and duality views, XML, and AI Vector Search with embeddings.
CH 22–31Every SQL functionCharacter, numeric, date, conversion, NULL, aggregate, analytic, LOB, hashing, UUID, and machine learning.
CH 32–33Changing dataINSERT, UPDATE, DELETE, MERGE, RETURNING, transactions, locking, and reservable columns.
CH 34–40Defining dataTables of every kind, views, indexes, domains, assertions, users, privileges, and the data dictionary.
CH 41–45The PL/SQL languageBlocks, cursors, records and collections, exceptions, and procedures and functions.
CH 46–49Packages to SQL macrosPackages, triggers, dynamic SQL, bulk processing, object types, pipelined functions, and SQL macros.
CH 50–58Built-in packagesDBMS_SQL, DBMS_LOB, UTL_FILE, UTL_HTTP, DBMS_SCHEDULER, queues, DBMS_CRYPTO, and DBMS_METADATA.
CH 59–66More packagesJSON and SODA, XML, DBMS_VECTOR and search, DBMS_MLE, redefinition, performance, PTFs, and the web toolkit.
Contents

Eight parts, 66 chapters

Read Part I to set up the database and meet Nimbus Air, then go to the chapter you need. Each part builds on the ones before it, and the index finds any feature by name.

Look inside
IChapters 1–5

Getting Started

The database, the tools, and the sample schema every example runs on.

  • 1How to Use This Book
  • 2Installing Oracle AI Database 26ai Free
  • 3SQL*Plus, SQLcl, and SQL Developer
  • 4Database Basics for Developers
  • 5The Nimbus Air Sample Schema
IIChapters 6–8

SQL Fundamentals

The building blocks of every statement.

  • 6Data Types
  • 7Literals, Operators, Expressions, Conditions, and Pseudocolumns
  • 8Format Models
IIIChapters 9–21

Querying Data

From SELECT basics to graphs, JSON, XML, and vectors.

  • 9SELECT Basics
  • 10Joins
  • 11Aggregation and Grouping
  • 12Subqueries and Set Operators
  • 13The WITH Clause and Recursive Queries
  • 14Hierarchical Queries
  • 15Analytic Queries and Windows
  • 16PIVOT, UNPIVOT, MATCH_RECOGNIZE, and MODEL
  • 17Flashback Queries, Sampling, and Partition-Extended Names
  • 18Property Graphs and SQL/PGQ
  • 19JSON in SQL
  • 20XML in SQL
  • 21AI Vector Search
IVChapters 22–31

SQL Functions Reference

Every SQL function, family by family.

  • 22Character Functions
  • 23Numeric and Bitwise Functions
  • 24Date, Time, and Interval Functions
  • 25Conversion Functions
  • 26NULL-Related, Comparison, and Conditional Functions
  • 27Aggregate Functions
  • 28Analytic Functions
  • 29LOB, Collection, Hashing, Encoding, and UUID Functions
  • 30Environment, Identifier, Object, and Other Functions
  • 31Machine Learning in the Database
VChapters 32–33

Changing Data

DML, and the transactions and locks around it.

  • 32INSERT, UPDATE, DELETE, and MERGE
  • 33Transactions and Locking
VIChapters 34–40

Defining Data

Tables, views, indexes, and every other schema object, with security and the dictionary.

  • 34Tables
  • 35Special Tables
  • 36Views and Materialized Views
  • 37Indexes
  • 38Sequences, Synonyms, Domains, Assertions, and Other Schema Objects
  • 39Users, Roles, and Privileges
  • 40The Data Dictionary
VIIChapters 41–49

PL/SQL

The language, from the first block to SQL macros.

  • 41PL/SQL Blocks and Language Basics
  • 42SQL in PL/SQL and Cursors
  • 43Records and Collections
  • 44Exceptions
  • 45Procedures and Functions
  • 46Packages
  • 47Triggers
  • 48Dynamic SQL and Bulk Processing
  • 49Advanced PL/SQL
VIIIChapters 50–66

Built-in PL/SQL Packages

Nearly 100 supplied packages for developers, each with examples.

  • 50Output, Utility, and Session Packages
  • 51DBMS_SQL
  • 52DBMS_LOB
  • 53Raw Data, Encoding, and Text Utilities
  • 54Files and Networking
  • 55DBMS_SCHEDULER and DBMS_JOB
  • 56Queues, Pipes, Alerts, and Change Notification
  • 57Security Packages
  • 58Metadata and Code Management
  • 59JSON and SODA in PL/SQL
  • 60XML in PL/SQL
  • 61Vector, Search, and Text Packages
  • 62DBMS_MLE
  • 63Flashback, Comparison, Redefinition, and Parallel Execution
  • 64Performance Packages
  • 65Generic Types and Polymorphic Table Functions
  • 66The PL/SQL Web Toolkit
A–CAppendices

More Packages, What's New, and Commands

The other supplied packages, the features new since 19c, and a reference of SQL*Plus and SQLcl commands.

  • AOther Supplied Packages
  • BWhat's New Since Oracle Database 19c
  • CSQL*Plus and SQLcl Commands
New since Oracle Database 19c

Everything 26ai adds for developers

Oracle AI Database 26ai is the long-term support release after 19c, with all that 21c and 23ai introduced. The book marks what is new where it is described, and Appendix B lists it all with the chapters.

21
AI Vector Searchthe VECTOR type, distance functions, vector indexes, and embeddings from an ONNX model inside the database
19
JSON-relational duality viewsthe JSON type, JSON collection tables, and JSON_TRANSFORM
18
SQL property graphsGRAPH_TABLE and SQL/PGQ over ordinary tables
6
BOOLEAN in SQLa SQL data type at last, and conditions as BOOLEAN expressions
38
Domains, assertions, and JavaScriptdata use case domains, constraints across rows and tables, and MLE modules
11
GROUP BY ALL and QUALIFYSELECT without FROM, the VALUES constructor, and IF [NOT] EXISTS in DDL
45features new since 19c in Appendix B, each with its chapter
677examples run on Oracle AI Database 26ai Free, release 23.26
949entries in the index, from ABS to XMLTYPE
56more supplied packages described in Appendix A

Run in 26ai

Every example ran in Oracle AI Database 26ai Free while the book was written.

Real output

No output was typed by hand: the book prints exactly what each example returned.

One sample schema

All examples use Nimbus Air, a fictional airline installed with one script.

Free to run

Oracle AI Database 26ai Free, SQLcl, and the code repository cost nothing.

Vinish Kapoor

Oracle ACE Pro · Oracle developer and architect

  • Oracle ACE every year since 2020
  • More than twenty years with SQL and PL/SQL
  • Author of two books on Oracle APEX 26.1
vinish.dev@vinish_kapooru/vinishkapoor
About the author

Two decades of SQL and PL/SQL

Database applications since 2001

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

A developer and architect

His work ranges from migrating large Oracle Forms systems to modern web applications to products of his own, such as the Rounds hospital management system and VinAura PDF, a visual report designer for Oracle APEX.

His third book

His earlier books, Oracle APEX 26.1: The Complete Guide and Oracle APEX 26.1 API by Example, build on the SQL and PL/SQL this one covers in full. His blog, vinish.dev, is read by developers around the world for its Oracle SQL, PL/SQL and APEX tutorials.

Every SQL function. Every statement. Every developer package. Each with a real example and its real output.

Get the book
  • Paperback, 700 pages
  • Kindle edition
  • Free code on GitHub
FAQ

Frequently asked

Anything else? Ask on vinish.dev.

Who is this book for?

Developers who write SQL and PL/SQL for Oracle Database — application and APEX developers, data engineers, and DBAs who write code — from those who know basic SQL to experienced developers moving from 19c to 26ai. Read Part I first, then go to the chapter you need.

Do I need Oracle AI Database 26ai?

The examples ran in Oracle AI Database 26ai Free, which costs nothing; Chapter 2 shows how to install it in a container with Docker or Podman, on Linux, or on Windows. Most of the SQL and PL/SQL also works in 19c. What is new since 19c is marked in the text, and Appendix B lists it.

What exactly does it cover?

Queries of every kind, every family of SQL functions, DML and DDL, the PL/SQL language from blocks to object types and SQL macros, and nearly 100 built-in packages, from DBMS_OUTPUT and DBMS_SQL to UTL_HTTP, DBMS_SCHEDULER, DBMS_CRYPTO, DBMS_VECTOR, and DBMS_MLE. Appendix A describes the other supplied packages, most of them for DBAs.

Is the output really from running the code?

Yes. A runner executed every example with SQLcl on Oracle AI Database 26ai Free, release 23.26, and the book prints what it returned. Where the output depends on when you run an example — the current date, generated identifiers, timings — Chapter 1 says so.

What is Nimbus Air?

The book's sample schema: a fictional airline with 14 tables — airports, routes, flights, aircraft, crew, customers, bookings, tickets, payments, and reviews — with realistic data, including JSON loyalty documents, time zones, and review texts for vector search. It installs with one script from the code repository.

Does it cover AI Vector Search?

Yes. Chapter 21 covers the VECTOR type, the distance functions, vector indexes, and semantic search with an embedding model loaded into the database. Chapter 61 covers DBMS_VECTOR, DBMS_VECTOR_CHAIN, DBMS_HYBRID_VECTOR, and DBMS_SEARCH.

Where is the code?

On GitHub, free: github.com/devvinish/oracle-database-26ai-book-code has all 677 examples with their output, and the scripts that install the Nimbus Air schema.

Is the paperback in color?

The paperback is printed in black and white on white paper, 7.5 by 9.25 inches, 700 pages, with a glossy cover. The Kindle edition shows the code in color.

Get the book

Paperback or Kindle, on Amazon

Both editions have the same 66 chapters, three appendices, and index. Pick the paperback for your desk and the Kindle edition for code in color on any screen.

  1. Get the bookFrom Amazon, in the edition you prefer.
  2. Install the database and Nimbus AirOracle AI Database 26ai Free and the sample schema, as Chapters 2 and 5 describe.
  3. Run the examplesLook up a feature, run its example, and compare your output with the book's.

Paperback

$39.99on Amazon.com

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

Kindle edition

$12.99on Amazon.com

  • Code in color
  • Reflowable on any Kindle
  • Linked contents and index
  • Kindle apps for phone, tablet and PC
Buy for Kindle
github.comdevvinish / oracle-database-26ai-book-code
Public

The code of the book, free: all 677 examples with the output they produced, and the scripts that install the Nimbus Air schema.

  • setup/nimbus/the Nimbus Air schema, its data, and its sample files
  • examples/one folder per topic, each example with its output
  • setup/login.sqlthe SQLcl settings the examples ran with
Open on GitHub
Or clone it
git clone https://github.com/devvinish/oracle-database-26ai-book-code.git

Oracle Database 26ai SQL and PL/SQL: The Complete Developer's Guide. Every SQL function, statement, and developer package, run in Oracle AI Database 26ai and shown with its real output.

Oracle Database 26ai SQL and PL/SQL · First Edition, 2026Written by Vinish Kapoor
00