Month: April 2025

The RETURNING INTO clause has been in Oracle for a long time — it lets you capture column values from DML statements (INSERT, UPDATE, DELETE) without doing a separate SELECT. Oracle 23ai extends this in two meaningful ways: it now works with multi-row DML, and it supports the OLD and NEW keywords to capture values before and after the change.

Classic single-row RETURNING (has always worked):

DECLARE
    v_new_id NUMBER;
BEGIN
    INSERT INTO orders (customer_id, status)
    VALUES (42, 'PENDING')
    RETURNING order_id INTO v_new_id;

    DBMS_OUTPUT.PUT_LINE('Created order: ' || v_new_id);
END;

New: OLD and NEW qualifiers:

DECLARE
    v_old_status VARCHAR2(20);
    v_new_status VARCHAR2(20);
BEGIN
    UPDATE orders
    SET    status = 'SHIPPED'
    WHERE  order_id = 1001
    RETURNING OLD status, NEW status
    INTO v_old_status, v_new_status;

    DBMS_OUTPUT.PUT_LINE('Changed: ' || v_old_status || ' -> ' || v_new_status);
END;

Bulk collect with multi-row DML:

DECLARE
    TYPE t_ids IS TABLE OF NUMBER;
    v_ids t_ids;
BEGIN
    DELETE FROM temp_orders
    WHERE  processed = 'Y'
    RETURNING order_id BULK COLLECT INTO v_ids;

    DBMS_OUTPUT.PUT_LINE('Deleted ' || v_ids.COUNT || ' orders');
END;

Why this matters: Audit logging, event sourcing, and change tracking patterns all benefit from capturing old/new values at the DML layer. Previously you had to use triggers or issue a SELECT before and after the DML. Now you can do it inline, atomically, with less code.

The most AI-forward feature in Oracle 23ai is AI Vector Search — a new VECTOR data type and a set of functions for storing, indexing, and querying vector embeddings directly in Oracle Database.

Vector embeddings are numerical representations of unstructured content (text, images, audio) generated by AI models. They’re the foundation of semantic search, recommendation systems, and RAG (Retrieval-Augmented Generation) architectures.

The VECTOR data type:

CREATE TABLE documents (
    doc_id      NUMBER,
    content     CLOB,
    embedding   VECTOR(1536, FLOAT32)  -- 1536 dimensions, OpenAI ada-002 format
);

Inserting a vector:

INSERT INTO documents (doc_id, content, embedding)
VALUES (1, 'Oracle 23ai brings AI features to the database.',
        TO_VECTOR('[0.023, -0.145, 0.871, ...]'));

Semantic similarity search:

SELECT doc_id, content,
       VECTOR_DISTANCE(embedding, :query_vector, COSINE) AS similarity
FROM   documents
ORDER BY similarity
FETCH FIRST 5 ROWS ONLY;

Creating a vector index for fast ANN search:

CREATE VECTOR INDEX docs_vector_idx
ON documents (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

The bigger picture: RAG in the database

The reason this matters is the architecture it enables. Instead of maintaining a separate vector database (Pinecone, Weaviate, Chroma), you can store embeddings alongside the source data in Oracle. Your SQL queries can combine vector similarity search with traditional relational filters:

SELECT content
FROM   documents
WHERE  created_date > DATE '2024-01-01'   -- relational filter
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH FIRST 10 ROWS ONLY;

No data movement, no synchronization, full ACID guarantees. This is Oracle’s AI play, and it’s well-designed.

Property graphs model data as nodes and edges — useful for social networks, supply chains, fraud detection, recommendation engines, and any domain where relationships are as important as the data itself. Oracle has had graph support for years (via Oracle Graph Server), but Oracle 23ai brings graph queries directly into SQL using the new ANSI SQL/PGQ standard.

Creating a property graph:

CREATE PROPERTY GRAPH company_graph
    VERTEX TABLES (employees KEY (employee_id))
    EDGE TABLES   (
        reports_to
            SOURCE KEY (employee_id) REFERENCES employees
            DESTINATION KEY (manager_id) REFERENCES employees
    );

Querying the graph with SQL/PGQ:

SELECT *
FROM GRAPH_TABLE (
    company_graph
    MATCH (e IS employees) -[r IS reports_to]-> (m IS employees)
    WHERE e.department_id = 10
    COLUMNS (e.first_name AS employee, m.first_name AS manager)
);

Finding paths of arbitrary depth:

-- Find all employees up to 3 levels below a given manager
SELECT *
FROM GRAPH_TABLE (
    company_graph
    MATCH (top) -[IS reports_to]->+ (sub)  -- + means one or more hops
    WHERE top.employee_id = 100
    COLUMNS (sub.employee_id, sub.first_name)
);

Why in SQL, not a separate graph query language?

This is the key insight. By embedding graph traversal in SQL, you can combine graph patterns with relational predicates, aggregations, and joins — in a single query. No need to move data to a dedicated graph database.

Oracle is the first major commercial RDBMS to implement the SQL/PGQ standard. This positions it well for use cases in fraud detection, network analysis, and knowledge graphs — all of which are growing rapidly in enterprise settings.

One of Oracle 23ai’s headline features is JSON Relational Duality Views. The concept is elegant: define a view once, and it automatically presents the same data as both structured relational tables and JSON documents. No duplication, no synchronization lag, no ETL.

Creating a duality view:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON {
    '_id'       : o.order_id,
    'status'    : o.status,
    'customer'  : o.customer_name,
    'items'     : [
        SELECT JSON {
            'product'  : oi.product_name,
            'qty'      : oi.quantity,
            'price'    : oi.unit_price
        }
        FROM order_items oi
        WHERE oi.order_id = o.order_id
    ]
}
FROM orders o WITH INSERT UPDATE DELETE;

Reading as JSON:

SELECT data FROM order_dv WHERE data.status = 'PENDING';

Reading as relational (same data):

SELECT * FROM orders WHERE status = 'PENDING';

Both queries hit the same underlying tables. No duplication.

Why this is architecturally significant

Modern applications often have mixed workloads. A REST API might need JSON documents. Analytics queries need relational structure. Traditional solutions involve maintaining both a relational database and a document store (like MongoDB) with synchronization middleware.

Duality Views eliminate that. The same Oracle database, the same tables, the same transaction — but surfaced as JSON for your Node.js app and as SQL for your BI tool.

ORDS integration: Oracle REST Data Services (ORDS) can automatically expose duality views as REST endpoints, letting you build MongoDB-style document APIs on top of relational data with minimal code.

This is a genuinely novel architecture that deserves its own blog series. For now, the key point: if you have applications that need document APIs and relational reporting, 23ai’s duality views are worth evaluating seriously.