Oracle PL/SQL 26ai — Detailed Course Notes

Comprehensive notes covering PL/SQL foundations, advanced programming, SQL simplifications, security, performance tuning, and capstone project for Oracle AI Database 26ai (Release 23.26.1).

📑 Table of Contents

Week 1 — PL/SQL Foundations & New Data Types Foundations

Review of PL/SQL Architecture

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension to SQL. Its architecture is built around the concept of blocks — the fundamental building units of PL/SQL code. A block consists of three sections:

PL/SQL supports variables of various data types (NUMBER, VARCHAR2, DATE, BOOLEAN, etc.), control structures (IF-THEN-ELSE, CASE, loops like FOR, WHILE, LOOP), and robust exception handling using EXCEPTION blocks and user-defined exceptions.

💡 Key Insight: PL/SQL seamlessly integrates SQL with procedural logic, enabling developers to build complex, data-centric applications that run directly inside the database — reducing network traffic and improving performance.

BOOLEAN Data Type — Now Fully Supported in SQL New

While BOOLEAN has been available in PL/SQL for many years, Oracle AI Database 26ai now fully supports the BOOLEAN data type in SQL as well. This is a significant enhancement that bridges the gap between PL/SQL and SQL, providing improved compatibility and consistency across the database.

Key capabilities introduced in 26ai:

-- Example: Creating a PL/SQL function with BOOLEAN parameter
CREATE OR REPLACE FUNCTION is_employee_active(p_emp_id NUMBER)
RETURN BOOLEAN
AS
    v_status VARCHAR2(1);
BEGIN
    SELECT status INTO v_status FROM employees WHERE emp_id = p_emp_id;
    RETURN (v_status = 'A');
END;
/

-- Now callable directly from SQL (new in 26ai!)
SELECT emp_name,
       CASE WHEN is_employee_active(emp_id) THEN 'Active' ELSE 'Inactive' END AS status
FROM employees;

-- Using BOOLEAN in PL/SQL with implicit conversion
DECLARE
    l_active BOOLEAN := TRUE;
    l_number NUMBER;
BEGIN
    l_number := l_active;  -- Implicit conversion (BOOLEAN to NUMBER)
    DBMS_OUTPUT.PUT_LINE('Active as number: ' || l_number);  -- Outputs: 1
END;
/

VECTOR Data Type for AI Workloads AI

Oracle AI Database 26ai introduces the VECTOR data type, providing native support for storing and manipulating vector embeddings directly in the database. This enables semantic search, similarity comparisons, and AI-powered analytics without moving data to external systems.

Key vector capabilities in PL/SQL:

-- Creating a table with a VECTOR column
CREATE TABLE product_catalog (
    product_id   NUMBER,
    product_name VARCHAR2(200),
    description  CLOB,
    embedding    VECTOR
);

-- PL/SQL block working with vectors
DECLARE
    v_vector1 VECTOR := VECTOR('[1.0, 2.0, 3.0]');
    v_vector2 VECTOR := VECTOR('[4.0, 5.0, 6.0]');
    v_distance NUMBER;
BEGIN
    -- Calculate Euclidean distance
    v_distance := VECTOR_DISTANCE(v_vector1, v_vector2, 'EUCLIDEAN');
    DBMS_OUTPUT.PUT_LINE('Distance: ' || v_distance);
    
    -- Dimension-wise addition
    -- (Vector arithmetic support in 26ai)
    -- v_sum := v_vector1 + v_vector2;
END;
/

Best Practices for PL/SQL Development in the AI Database

Week 2 — Advanced PL/SQL Programming & SQL Integration Advanced

Complex Procedures, Functions, and Packages

PL/SQL supports procedures (subprograms that perform actions), functions (subprograms that return values), and packages (containers that group related subprograms, variables, and cursors). Packages offer several advantages:

-- Package specification
CREATE OR REPLACE PACKAGE order_mgmt AS
    PROCEDURE place_order(p_customer_id NUMBER, p_product_id NUMBER, p_quantity NUMBER);
    FUNCTION get_order_total(p_order_id NUMBER) RETURN NUMBER;
    PROCEDURE update_order_status(p_order_id NUMBER, p_status VARCHAR2);
END order_mgmt;
/

-- Package body (implementation)
CREATE OR REPLACE PACKAGE BODY order_mgmt AS
    -- Private variable (persists across calls)
    v_log_level VARCHAR2(10) := 'INFO';
    
    -- Private helper procedure
    PROCEDURE log_message(p_msg VARCHAR2) IS
    BEGIN
        IF v_log_level = 'DEBUG' THEN
            DBMS_OUTPUT.PUT_LINE(SYSDATE || ' - ' || p_msg);
        END IF;
    END log_message;
    
    PROCEDURE place_order(p_customer_id NUMBER, p_product_id NUMBER, p_quantity NUMBER) IS
    BEGIN
        INSERT INTO orders (order_id, customer_id, order_date, status)
        VALUES (order_seq.NEXTVAL, p_customer_id, SYSDATE, 'PENDING');
        
        INSERT INTO order_items (order_id, product_id, quantity)
        VALUES (order_seq.CURRVAL, p_product_id, p_quantity);
        
        log_message('Order placed for customer ' || p_customer_id);
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            log_message('Error placing order: ' || SQLERRM);
            RAISE;
    END place_order;
    
    FUNCTION get_order_total(p_order_id NUMBER) RETURN NUMBER IS
        v_total NUMBER;
    BEGIN
        SELECT SUM(quantity * price) INTO v_total
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.order_id = p_order_id;
        RETURN NVL(v_total, 0);
    END get_order_total;
    
    PROCEDURE update_order_status(p_order_id NUMBER, p_status VARCHAR2) IS
    BEGIN
        UPDATE orders SET status = p_status WHERE order_id = p_order_id;
        log_message('Order ' || p_order_id || ' status updated to ' || p_status);
        COMMIT;
    END update_order_status;
END order_mgmt;
/

Bulk Operations for High-Performance Data Processing

Bulk operations (BULK COLLECT and FORALL) significantly improve performance when processing large datasets by reducing context switching between the SQL and PL/SQL engines.

DECLARE
    TYPE emp_rec_type IS RECORD (emp_id NUMBER, salary NUMBER);
    TYPE emp_tab_type IS TABLE OF emp_rec_type;
    l_employees emp_tab_type;
    
    TYPE id_tab_type IS TABLE OF NUMBER;
    l_ids id_tab_type := id_tab_type();
    
    CURSOR c_emp IS
        SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
    -- BULK COLLECT: Fetch all rows at once
    OPEN c_emp;
    FETCH c_emp BULK COLLECT INTO l_employees;
    CLOSE c_emp;
    
    -- Prepare IDs for bulk update
    FOR i IN 1..l_employees.COUNT LOOP
        l_ids.EXTEND;
        l_ids(i) := l_employees(i).emp_id;
    END LOOP;
    
    -- FORALL: Bulk update all salaries
    FORALL i IN 1..l_ids.COUNT
        UPDATE employees
        SET salary = salary * 1.10
        WHERE employee_id = l_ids(i);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' employees.');
END;
/

TABLE Functions and PIPELINED Functions

Table functions allow PL/SQL functions to return collections that can be queried like database tables using the TABLE operator. Pipelined functions enhance this by streaming results row-by-row as they are generated, rather than building the entire collection in memory.

-- Pipelined function example
CREATE OR REPLACE FUNCTION get_employee_data(p_dept_id NUMBER)
RETURN SYS_REFCURSOR PIPELINED
AS
    CURSOR c_emp IS
        SELECT employee_id, first_name || ' ' || last_name AS full_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR rec IN c_emp LOOP
        PIPE ROW(rec);
    END LOOP;
    RETURN;
END;
/

-- Query the pipelined function
SELECT * FROM TABLE(get_employee_data(10));

Working with JSON in PL/SQL

Oracle AI Database 26ai enhances JSON support in PL/SQL with more flexible constructors and aggregate type conversions.

-- JSON_ARRAY with subquery (new in 23ai/26ai)
SELECT JSON_ARRAY(
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = 10
) AS emp_json_array
FROM DUAL;

-- PL/SQL: Converting a collection to JSON
DECLARE
    TYPE t_emp_list IS TABLE OF VARCHAR2(100);
    l_emps t_emp_list := t_emp_list('Alice', 'Bob', 'Charlie');
    l_json JSON;
BEGIN
    l_json := JSON(l_emps);  -- Constructor accepts collection
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(l_json));
END;
/

Dynamic SQL with EXECUTE IMMEDIATE and DBMS_SQL

Dynamic SQL allows you to construct and execute SQL statements at runtime. Use EXECUTE IMMEDIATE for simple dynamic SQL, and DBMS_SQL for more complex scenarios requiring dynamic parsing, binding, and column definition.

-- EXECUTE IMMEDIATE with bind variables
CREATE OR REPLACE PROCEDURE dynamic_query(p_table_name VARCHAR2, p_column_name VARCHAR2, p_value VARCHAR2) IS
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name || ' WHERE ' || p_column_name || ' = :val';
    EXECUTE IMMEDIATE v_sql INTO v_count USING p_value;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

Autonomous Transactions

Autonomous Transactions are independent transactions that are initiated within another transaction. They allow you to commit or rollback changes independently of the parent transaction — useful for logging, auditing, and error handling.

CREATE OR REPLACE PROCEDURE log_error(p_error_code NUMBER, p_error_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- This procedure runs in its own transaction
BEGIN
    INSERT INTO error_log (log_id, log_date, error_code, error_message)
    VALUES (error_log_seq.NEXTVAL, SYSDATE, p_error_code, p_error_msg);
    COMMIT;  -- Commits the log entry independently
END log_error;
/

Week 3 — SQL Simplifications & Language Enhancements Productivity

Oracle AI Database 26ai introduces several SQL syntax simplifications that make code cleaner, more readable, and less error-prone.

GROUP BY ALL

GROUP BY ALL automatically groups by all non-aggregated columns in the query, eliminating the need to list them explicitly. This reduces repetitive SQL and prevents errors when columns are added or removed.

-- Traditional GROUP BY
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

-- New: GROUP BY ALL (automatically groups by department_id and job_id)
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ALL;

Time Bucketing Functions

New time bucketing functions simplify time-series analysis by grouping datetime values into buckets of specified intervals (e.g., hourly, daily, weekly).

-- Bucket sales by week
SELECT TIME_BUCKET(week, order_date) AS week_start,
       SUM(total_amount) AS weekly_sales
FROM orders
GROUP BY TIME_BUCKET(week, order_date)
ORDER BY week_start;

Non-Positional INSERT

Insert using column names without positional mapping. Values are matched by column name, reducing the risk of data corruption when the table schema changes.

INSERT INTO employees
    (emp_id, emp_name, hire_date, salary)
VALUES
    (101, 'Alice', SYSDATE, 50000);  -- Values map by column name, not position

Enhanced CASE Control Structures

PL/SQL now supports more flexible CASE expressions, including the ability to use BOOLEAN conditions directly.

DECLARE
    v_status VARCHAR2(10) := 'ACTIVE';
    v_active BOOLEAN;
BEGIN
    v_active := CASE v_status
                    WHEN 'ACTIVE' THEN TRUE
                    WHEN 'INACTIVE' THEN FALSE
                    ELSE NULL
                END;
    -- Boolean CASE is now fully supported in SQL as well
END;
/

Assertions New

Assertions are a new type of integrity constraint that can enforce complex business rules across multiple tables. Unlike traditional check constraints (limited to a single table), assertions can reference multiple tables and ensure that DML operations always honour the defined rules.

An assertion contains a boolean expression (typically a subquery in a NOT EXISTS clause). If the expression returns false at the end of a transaction, the entire transaction fails.

-- Ensure that an employee's salary never exceeds their department's budget
CREATE ASSERTION salary_within_budget
CHECK (
    NOT EXISTS (
        SELECT 1
        FROM employees e
        JOIN departments d ON e.dept_id = d.id
        WHERE e.salary > d.budget
    )
);

-- Ensure that total debits equal total credits across all ledgers
CREATE ASSERTION balanced_ledger
CHECK (
    (SELECT SUM(amount) FROM transactions WHERE type = 'DEBIT') =
    (SELECT SUM(amount) FROM transactions WHERE type = 'CREDIT')
);

Calendar Functions & Aggregation Filters

SELECT department_id,
       SUM(salary) AS total_salary,
       SUM(salary) FILTER (WHERE bonus > 1000) AS bonus_above_1000,
       SUM(salary) FILTER (WHERE hire_date >= DATE '2025-01-01') AS new_hires_salary
FROM employees
GROUP BY department_id;

Data Use Case Domains New

Data Use Case Domains allow developers to declare the intended usage of a column (e.g., email, URL, password, currency) in a centralized manner. Applications can use this metadata to automatically generate validation code or verify values.

-- Define a domain for email addresses
CREATE DOMAIN email_domain AS VARCHAR2(320)
  CONSTRAINT email_format CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

-- Define a domain for UK postcodes
CREATE DOMAIN uk_postcode_domain AS VARCHAR2(10)
  CONSTRAINT postcode_format CHECK (REGEXP_LIKE(VALUE, '^([A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2})$'));

-- Use domains in tables
CREATE TABLE customers (
    cust_id   NUMBER,
    cust_name VARCHAR2(100),
    email     email_domain,      -- Inherits validation
    postcode  uk_postcode_domain -- Inherits validation
);

Improved Error Messages

Oracle AI Database 26ai provides more detailed and actionable error messages, making debugging significantly easier. Error messages now include context-specific suggestions and references to documentation.

Week 4 — Security: SQL Firewall & Unified Auditing Security

Oracle SQL Firewall New

SQL Firewall is a new security feature built directly into the Oracle AI Database 26ai kernel. It inspects all incoming SQL statements — whether local or over the network, encrypted or clear text — and ensures that only explicitly authorized SQL is executed.

SQL Firewall provides real-time protection against:

🛡️ Key Advantage: SQL Firewall cannot be bypassed because it's embedded in the database kernel, unlike external Web Application Firewalls (WAFs).

Configuring SQL Firewall from PL/SQL

SQL Firewall is managed using the DBMS_SQL_FIREWALL package. The typical workflow involves:

  1. Enable SQL Firewall on the database.
  2. Start SQL collection for a specific database user to capture expected application SQL.
  3. Generate a policy (allowlist) from the collected SQL.
  4. Enable the policy to start enforcing protection.
-- Connect as a privileged user (e.g., SYS or a user with ADMIN privilege)
-- Enable SQL Firewall
EXEC DBMS_SQL_FIREWALL.ENABLE;

-- Start SQL collection for application user 'APP_USER'
EXEC DBMS_SQL_FIREWALL.START_COLLECTION('APP_USER');

-- After running the application workload for a sufficient period:
-- Generate policy from collected SQL
EXEC DBMS_SQL_FIREWALL.GENERATE_POLICY('APP_USER');

-- Enable the policy (enforcement mode)
EXEC DBMS_SQL_FIREWALL.ENABLE_POLICY('APP_USER');

-- View SQL Firewall violations
SELECT * FROM DBA_SQL_FIREWALL_VIOLATIONS;

Session Context Allowlisting

SQL Firewall policies can restrict SQL execution based on session context — client IP address, OS username, and OS program name. This ensures that only trusted connections from known applications can execute sensitive SQL.

-- Add session context to a policy
BEGIN
    DBMS_SQL_FIREWALL.ADD_CONTEXT(
        user_name => 'APP_USER',
        context   => SYS_CONTEXT('USERENV', 'IP_ADDRESS') = '192.168.1.100'
    );
END;
/

Unified Auditing for PL/SQL Execution

Unified Auditing provides a single, consolidated audit trail for all database activities, including PL/SQL execution. It captures detailed information about who executed what, when, and from where.

-- Create an audit policy for PL/SQL execution
CREATE AUDIT POLICY plsql_audit_policy
    ACTIONS EXECUTE ON SCHEMA.APP_PACKAGE;

-- Enable the policy
AUDIT POLICY plsql_audit_policy;

-- Query audit records
SELECT username, obj_name, action_name, timestamp
FROM unified_audit_trail
WHERE obj_name = 'APP_PACKAGE'
ORDER BY timestamp DESC;

Schema-Level Privileges New

System privileges can now be granted at the schema level, simplifying privilege management. Instead of granting privileges individually on each object, administrators can grant schema-level privileges that apply to all objects in the schema.

-- Grant schema-level privileges
GRANT SELECT ANY TABLE ON SCHEMA hr TO app_user;
GRANT EXECUTE ANY PROCEDURE ON SCHEMA hr TO app_user;

Azure AD OAuth2 Integration New

Oracle AI Database 26ai now supports Azure AD OAuth2 integration, enabling single sign-on (SSO) to Oracle AI Database instances from Microsoft Azure Cloud.

Week 5 — Performance Tuning & Optimization Performance

Dynamic Statistics for PL/SQL Functions

Dynamic Statistics improve the optimizer's ability to estimate cardinality when PL/SQL functions are used in SQL queries. Oracle can now gather statistics dynamically during query execution for better plan decisions.

Automatic SQL Transpiler New

The Automatic SQL Transpiler is a powerful new feature in Oracle AI Database 26ai that converts PL/SQL functions into SQL expressions whenever possible, eliminating the overhead of context switching between the SQL and PL/SQL engines.

When the SQL_TRANSPILER initialization parameter is set to ON, the database automatically rewrites queries to inline function logic directly into the SQL statement.

-- Create a function
CREATE OR REPLACE FUNCTION add_numbers(p1 NUMBER, p2 NUMBER) RETURN NUMBER AS
BEGIN
    RETURN p1 + p2;
END;
/

-- Query using the function (without transpiler)
SELECT id, val1, val2 FROM t1 WHERE add_numbers(val1, val2) = 300;
-- Execution plan shows: filter("ADD_NUMBERS"("VAL1","VAL2")=300)

-- Enable the transpiler for the session
ALTER SESSION SET sql_transpiler = 'ON';

-- Re-run the query
SELECT id, val1, val2 FROM t1 WHERE add_numbers(val1, val2) = 300;
-- Execution plan now shows: filter("VAL1"+"VAL2"=300)  -- Function is inlined!

The transpiler is also configurable at the system level:

-- Enable globally
ALTER SYSTEM SET sql_transpiler = ON;

Optimizing PL/SQL Function Calls in SQL

Beyond the automatic transpiler, best practices for optimizing function calls include:

SQL Diagnostic Report

The SQL Diagnostic Report provides deep performance analysis including plan history, optimizer statistics, and index recommendations. Generate a report using:

-- Generate SQL Diagnostic Report for a specific SQL ID
SELECT DBMS_SQLDIAG.REPORT_SQL_DIAGNOSTIC(sql_id => 'dcttgua9qy5x4')
FROM DUAL;

Priority Transactions New

Priority Transactions allow the database to automatically abort low-priority transactions that are blocking high-priority transactions, reducing administrative burden and maintaining high transaction throughput.

-- Set transaction priority
SET TRANSACTION PRIORITY HIGH;

-- Perform critical operation
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 12345;
COMMIT;

Telemetry Streaming

Telemetry Streaming provides ultra-fast metrics ingestion for observability using REST, SQL, and PromQL, enabling real-time visibility into database performance, query latency, and vector throughput.

Performance Tuning for Vector Operations

Week 6 — Advanced Features & Capstone Project Capstone

Wide Tables — Up to 4096 Columns New

Oracle AI Database 26ai increases the maximum number of columns per table from 1,000 to 4,096 by setting MAX_COLUMNS = EXTENDED. This is critical for AI and IoT applications that generate thousands of feature columns.

-- Enable Wide Tables (requires COMPATIBLE >= 23.0.0.0)
ALTER SYSTEM SET MAX_COLUMNS = EXTENDED;

-- Create a table with 4000 columns
CREATE TABLE wide_feature_table (
    record_id   NUMBER,
    timestamp   TIMESTAMP,
    feature_1   NUMBER,
    feature_2   NUMBER,
    -- ... up to 4096 columns ...
);

Important notes:

PL/SQL Procedures for Managing Wide Tables

Managing tables with thousands of columns requires careful PL/SQL design. Consider dynamic SQL for operations that need to reference many columns.

CREATE OR REPLACE PROCEDURE insert_wide_record(
    p_record_id NUMBER,
    p_features  SYS.ODCINUMBERLIST  -- Collection of feature values
) IS
    v_sql VARCHAR2(4000);
BEGIN
    -- Dynamic SQL to insert into wide table
    v_sql := 'INSERT INTO wide_feature_table (record_id, timestamp, ' ||
             'feature_1, feature_2, feature_3) VALUES (:1, SYSDATE, :2, :3, :4)';
    EXECUTE IMMEDIATE v_sql USING p_record_id, p_features(1), p_features(2), p_features(3);
END;
/

Integrating PL/SQL with AI Vector Search, JSON Duality, and Property Graphs

The power of Oracle AI Database 26ai comes from combining its AI capabilities with traditional data management. PL/SQL is the orchestrator that ties these together.

-- PL/SQL procedure that combines Vector Search with JSON Duality
CREATE OR REPLACE PROCEDURE find_similar_products(
    p_search_vector VECTOR,
    p_category      VARCHAR2,
    p_limit         NUMBER
) IS
    v_sql VARCHAR2(4000);
    v_cursor SYS_REFCURSOR;
BEGIN
    -- Hybrid search: vector similarity + relational filter
    v_sql := 'SELECT product_id, product_name, ' ||
             'VECTOR_DISTANCE(embedding, :1) AS similarity ' ||
             'FROM products ' ||
             'WHERE category = :2 ' ||
             'ORDER BY similarity ' ||
             'FETCH FIRST :3 ROWS ONLY';
    
    OPEN v_cursor FOR v_sql USING p_search_vector, p_category, p_limit;
    -- Process results...
END;
/

Blockchain Table Log History New

Blockchain Table Log History provides an immutable audit trail of changes to regular tables, maintained as part of Flashback Data Archive. This is essential for financial ledgers, compliance, and certified audit processes.

-- Enable blockchain logging on a table
ALTER TABLE financial_transactions ADD PERIOD FOR LOG_HISTORY;

Sessionless Transactions and Lock-Free Reservations New

Lock-Free Reservations allow applications to reserve part of a column value (e.g., inventory quantity or bank balance) without locking the row. Transactions hold reservations in a journal and only process them on commit, dramatically improving concurrency in high-contention scenarios.

-- Create a table with a reservable column
CREATE TABLE inventory (
    product_id   NUMBER PRIMARY KEY,
    quantity     NUMBER RESERVABLE  -- Enables lock-free reservations
);

-- Reserve quantity without blocking other transactions
UPDATE inventory
SET quantity = quantity - 10
WHERE product_id = 12345
  AND quantity >= 10
RESERVE FOR UPDATE;

-- The actual update is only applied on COMMIT
COMMIT;

Deploying PL/SQL Applications with CI/CD Pipelines

Capstone Project

Project Objective: Design and implement an enterprise PL/SQL application that integrates AI Vector Search, JSON processing, SQL Firewall security, and performance optimization in a real-world business scenario.

Deliverables:

🎯 Project Goal: Build a production-ready PL/SQL application that demonstrates mastery of Oracle AI Database 26ai features, including vector operations, JSON processing, SQL Firewall security, and performance optimization.

📚 References:

All documentation links are for Oracle AI Database 26ai (Release 23.26.1).

© Copyright Westminster College of AI and Quantum Computing Limited, 2026. | info@wcc.co.uk | https://wcc.co.uk