Oracle AI Database 26ai lets you place an expression directly in the partitioning clause. You no longer need to expose a virtual column only to use its result as the partition key.
Partition by expression works with the basic RANGE, LIST and HASH methods. Oracle creates and manages a hidden virtual column internally for each expression.
Oracle AI Database 26ai allows a WITH clause inside another WITH clause. Helper CTEs can now stay close to the query that uses them instead of being moved to the top level.
Earlier Oracle releases required flattening the CTEs or using inline views.
WITH outer_cte AS
(
WITH inner_cte AS
(
SELECT ...
)
SELECT ...
FROM inner_cte
)
SELECT ...
FROM outer_cte;
In Oracle AI Database 26ai the WAIT and NOWAIT clauses are available for all main DML statements: INSERT, UPDATE, DELETE and MERGE.
They let an application decide what to do when a DML statement tries to modify a row that is already locked by another transaction:
NOWAIT – fail immediately instead of waiting for the lock.WAIT n – wait for the specified time and fail if the row is still locked.WAIT FOREVER – keep the traditional Oracle behavior and wait indefinitely.This makes lock handling explicit at statement level and avoids changing a session-wide setting.
INSERT ... NOWAIT; INSERT ... WAIT 5 SECONDS; UPDATE ... WHERE ... WAIT 500 MILLISECONDS; DELETE ... WHERE ... WAIT 2 SECONDS; MERGE ... WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ... WAIT FOREVER;
The supported time units are SECONDS, MILLISECONDS and MICROSECONDS. If the unit is omitted, Oracle uses seconds.
If neither clause is specified, the statement keeps the default behavior and waits indefinitely for the row lock.
Oracle AI Database 26ai introduces aggregation filters using the SQL-standard FILTER (WHERE ...) clause. It lets you apply a condition to one aggregate function without filtering rows for the whole query.
This is especially useful when a report needs several conditional totals or counts. Before this enhancement, the usual solution was to put a CASE expression inside every aggregate function. The new syntax is shorter and makes the intention of the query much easier to read.
aggregate_function ( aggregate_function_arguments )
FILTER (WHERE condition)The filter belongs to the aggregate function immediately before it. Only rows for which the condition is true are passed to that aggregate.
Type BOOLEAN is extended in 23/26AI and supports more values for sql and PL/SQL
| Type | Accepted values |
|---|---|
| BOOLEAN literals | TRUE, FALSE |
| Strings |
|
| Numbers | 1, 0 |
| NULL | NULL |
Oracle has supported the RETURNING clause in DML for years, but Oracle Database 23ai / 26ai significantly improves its usability by allowing direct access to OLD and NEW column values in INSERT UPDATE DELETE MERGE statements.
This makes it much easier to capture before-and-after data without triggers or extra queries.
Oracle Database 23ai (and 26ai) introduces table value constructors using the VALUES clause.
This lets you define inline row sets directly in SQL—no DUAL, no UNION ALL, no temporary tables.
You can use VALUES in SELECT, INSERT, UPDATE, MERGE, and WITH clauses.
If you’ve ever written analytics queries with RANK(), DENSE_RANK(), ROW_NUMBER(), AVG() OVER(...), etc., you know the pain:
SELECTWHEREOracle Database 26ai fixes this nicely with QUALIFY.
SELECT
...
RANK() OVER (ORDER BY revenue DESC) AS rev_rank
FROM test_tbl
QUALIFY rev_rank <= 5
ORDER BY rev_rank, name;QUALIFY is evaluated after window functions are computed, so you can filter on them directly — no nesting needed.
Oracle Database 23ai introduces GROUP BY ALL, a small SQL feature that removes the need to manually list columns in the GROUP BY clause.
UPDATE and DELETE in Oracle Database 23ai / 26aiOne of the most elegant SQL enhancements in Oracle Database 23ai (continued in 26ai) is the support for direct joins in UPDATE and DELETE statements. This syntax lets you perform DML that references other tables using a FROM clause—without resorting to nested subqueries or EXISTS.
The new syntax
UPDATE with direct join
UPDATE target_table alias
SET column = expression [, column = expression, ...]
FROM join_table jt1 [JOIN jt2 ON ...]
WHERE join_condition;
DELETE with direct join
DELETE target_table alias
FROM join_table jt1 [JOIN jt2 ON ...]
WHERE join_condition;
Oracle determines the target of the DML by the table that follows the UPDATE or DELETE keyword.