Month: May 2025

Oracle 23ai allows table aliases in UPDATE and DELETE statements, making complex DML with subqueries and correlated references significantly more readable.

UPDATE with a table alias:

Before 23ai, the target table in UPDATE couldn’t easily have an alias in subquery references. Now:

UPDATE employees e
SET    e.salary = e.salary * 1.10
WHERE  e.department_id IN (
    SELECT d.department_id
    FROM   departments d
    WHERE  d.location_code = 'NYC'
);

The alias e is now cleanly scoped across the entire statement.

DELETE with a table alias:

DELETE FROM order_archive oa
WHERE  oa.archived_date < ADD_MONTHS(SYSDATE, -24)
AND    oa.customer_id IN (
    SELECT c.customer_id
    FROM   customers c
    WHERE  c.account_status = 'CLOSED'
);

Combining with direct joins (covered in Feb):

UPDATE employees e
JOIN   performance_reviews pr ON pr.employee_id = e.employee_id
SET    e.rating = pr.final_rating,
       e.last_review_date = pr.review_date
WHERE  pr.review_year = 2024
AND    e.status = 'ACTIVE';

While table aliases in DML were partially supported before (especially in correlated subqueries), 23ai formalizes and extends this capability to work cleanly with the new direct JOIN syntax. Together, they make multi-table DML in Oracle as readable as equivalent SELECT queries — which has always been the goal of SQL ergonomics.

Following up on last month’s SQL/PGQ introduction, let’s go deeper into the GRAPH_TABLE function — the SQL construct that makes graph traversal queries possible in Oracle 23ai.

GRAPH_TABLE appears in the FROM clause and returns a relational result set from a graph pattern match. This means you can use it exactly like any other table or view — filter it with WHERE, join it with other tables, aggregate it with GROUP BY.

Detecting fraud rings (connected accounts):

SELECT account_id, connected_account, hops
FROM GRAPH_TABLE (
    transaction_graph
    MATCH (a IS accounts) -[IS transfers]->{2,5} (b IS accounts)
    WHERE a.flagged = 1
    COLUMNS (
        a.account_id,
        b.account_id AS connected_account,
        GRAPH_PATH_LENGTH() AS hops
    )
)
WHERE hops <= 3;

Finding shortest path:

SELECT *
FROM GRAPH_TABLE (
    supply_chain_graph
    MATCH SHORTEST (supplier) -[IS supplies]->+ (product)
    WHERE supplier.country = 'US' AND product.sku = 'SKU-9981'
    COLUMNS (LISTAGG(supplier.name, ' -> ') WITHIN GROUP (GRAPH ORDER) AS supply_path)
);

Combining with relational data:

-- Which customers are connected to known fraudsters, and what's their credit limit?
SELECT g.customer_id, c.credit_limit, g.hops
FROM GRAPH_TABLE (
    fraud_network_graph
    MATCH (seed) -[IS knows]->{1,3} (connected)
    WHERE seed.is_fraudster = TRUE
    COLUMNS (connected.customer_id, GRAPH_PATH_LENGTH() AS hops)
) g
JOIN customers c ON c.customer_id = g.customer_id
WHERE c.credit_limit > 10000;

The power of GRAPH_TABLE is not graph traversal in isolation — it’s the seamless combination of graph patterns with relational operations. This is what sets Oracle’s implementation apart from standalone graph databases.

Oracle 23ai introduces Select AI — a feature that lets you query your Oracle database using plain English (or other natural languages), which Oracle translates into SQL using an LLM.

It’s not magic. It’s a bridge between a large language model (OpenAI, Cohere, or others) and your database schema. But it’s designed carefully, with schema awareness and configurable profiles.

Setting up a Select AI profile:

BEGIN
    DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name   => 'hr_assistant',
        attributes     => '{
            "provider"      : "openai",
            "credential_name": "openai_cred",
            "object_list"   : [
                {"owner": "HR", "name": "EMPLOYEES"},
                {"owner": "HR", "name": "DEPARTMENTS"}
            ]
        }'
    );
END;

Querying in natural language:

SELECT AI 'How many employees are in the Sales department?'
USING PROFILE hr_assistant;

Oracle translates this to the appropriate SQL and returns the results.

Modes of operation:

-- See the SQL that was generated (transparency mode)
SELECT AI SHOWSQL 'List top 5 earning employees by department'
USING PROFILE hr_assistant;

-- Explain the results in natural language
SELECT AI NARRATE 'What was the headcount change from 2023 to 2024?'
USING PROFILE hr_assistant;

Honest caveats

Select AI depends on an external LLM provider (it currently doesn’t use a built-in model). The quality of generated SQL depends heavily on schema naming, documentation, and the complexity of the question. It works best on well-named schemas with reasonable cardinality — it will struggle with highly normalized schemas or ambiguous business terminology.

But as a natural language layer on top of structured data, it’s a legitimate tool for non-SQL users. Executive dashboards, self-service analytics, and chatbot integrations are all viable use cases.