Oracle SQL 26ai

6 Saturdays (9am to 11am) | Intermediate to Advanced | Course Notes

Course Content

Week 1 — SQL Foundations & AI Vector Search

• Review of core SQL concepts: SELECT, joins, subqueries, and set operations
• Introduction to Oracle AI Database 26ai SQL enhancements
• Understanding the native VECTOR data type and its use in AI workloads
• Building vector indexes (HNSW) for high-performance similarity search
• Writing SQL queries with vector search: VECTOR_DISTANCE and VECTOR_EMBEDDING functions
• Combining vector searches with traditional relational predicates, text, JSON, and graph queries
• Case study: Semantic search on unstructured data (documents, images)
• Performance considerations for vector queries with concurrent DML

Week 2 — JSON Relational Duality & New Data Types

• Introduction to JSON Relational Duality: transparently accessing data as JSON or relational tables
• Creating duality views with CREATE JSON DUALITY VIEW
• Using SQL/JSON functions (JSON_EXISTS, JSON_VALUE, JSON_QUERY) on duality views
• Transactional consistency and ACID guarantees with duality views
• Support for the BOOLEAN data type in Oracle Machine Learning and SQL
• Using BOOLEAN in SQL expressions and PL/SQL blocks
• Handling JSON documents with nested structures and arrays
• Performance tuning for JSON-based applications

Week 3 — Operational Property Graphs & Graph Queries

• Introduction to Property Graph model in Oracle Database
• Creating and populating property graphs using SQL and CREATE PROPERTY GRAPH
• Querying graphs using the new SQL-based graph syntax (SQL/PGQ)
• Performing graph analytics: shortest path, connectivity, and community detection
• Combining graph queries with relational and vector searches
• Using graph views and materialized graph snapshots for performance
• Real-time graph analysis against operational data without ETL
• Integration with Oracle Graph Studio and visualization tools

Week 4 — SQL Firewall, Security & Data Use Case Domains

• Understanding the SQL Firewall: real-time protection against SQL injection and unauthorized queries
• Configuring SQL Firewall policies and whitelisting safe SQL patterns
• Monitoring and logging SQL Firewall events for compliance and forensic analysis
• Declaring Data Use Case Domains for columns (email, URL, password, etc.)
• Using domain metadata for automatic validation and code generation
• Implementing schema-level privileges for simplified access control
• Combining SQL Firewall with unified auditing for comprehensive security
• Best practices for securing SQL applications in the AI database

Week 5 — SQL Simplifications & Performance Tuning

• New SQL simplifications: time bucketing functions for time-series analysis
• Non-positional INSERT syntax (using column names)
• GROUP BY ALL for aggregated rollups without explicit grouping
• Using EXCEPT, INTERSECT, and other set operators with the new enhancements
• Understanding optimizer statistics and execution plans in 26ai
• Performance tuning for AI Vector Search workloads — indexing strategies and cost-based optimizer
• Diagnosing slow queries using SQL Diagnostic Report (deep-level HTML reports with plan history, optimizer stats, and index details)
• Monitoring query performance with Telemetry Streaming (REST, SQL, PromQL)

Week 6 — Advanced PL/SQL, Automation & Capstone Project

• Writing PL/SQL procedures and functions for complex business logic
• Using dynamic SQL and native dynamic SQL for flexible applications
• Leveraging SQL Firewall and auditing in PL/SQL contexts
• Automating repetitive tasks using Oracle Scheduler and DBMS_SCHEDULER
• Exporting and importing data using Data Pump (the original EXP utility is desupported)
• Integrating SQL with REST endpoints and external APIs
• Understanding the deprecation of EM Express and using SQL Developer or OCI Database Management
• Capstone Project: Build a complete application using AI Vector Search, JSON Duality, and graph queries to solve a real-world business problem, with integrated security and performance optimization

Project: Design and implement a multi-modal SQL application combining vector search, JSON duality, and property graph queries for a unified data platform.

Starting Date: 4th July 2026
Course Fee: £2990