Oracle 26ai: 15 Key Developer Features Unveiled

Preface​

The official Oracle new features website lists 434 new features for Oracle AI Database 26ai. The address is: https://apex.oracle.com/database-features.

Oracle AI Database 26ai is available on Oracle Database Appliance, Oracle Exadata Database Machine, Autonomous Database, Exadata Database Service, Exadata Database Service on Exascale Infrastructure, Exadata Cloud@Customer, and Base Database Service. Oracle Exadata Database Service and Autonomous Database are available on OCI, Azure, Google Cloud, and AWS.

In the last two years, the most talked-about topics are AI and Vector Databases: vector data types, vector indexes, vector distance functions, support for ONNX format models, vector functions, vector memory, etc. I have previously translated official documentation and written about "Oracle 23ai New Features: AI Vector Search". However, it's likely updated now, and some content might differ. Let's skip that for now; you can first check out "Discussing Oracle 23ai New Features, Related Changes, and Deprecated Functions". Below, I have selected some new features related to developers at the SQL level, hoping it will be useful for professionals working with domestic and other databases.

​Installing 26ai FREE Version on Oracle Linux8​

Here is the latest architecture diagram for 26ai. It's different from versions seen before and deserves further study.

Installing 26ai on Oracle Linux8 is very simple. If your virtual machine can connect to the internet for online downloads, it's even easier, requiring just three steps.

-- Step 1: Download the preinstall and ai-database-free-26ai RPM packages for your system version
wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-ai-database-preinstall-26ai-1.0-1.el8.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-ai-database-free-26ai-23.26.0-1.el8.x86_64.rpm

-- Step 2: Install the preinstall and ai-database-free-26ai RPM packages
dnf install oracle-ai-database-preinstall-26ai-1.0-1.el8.x86_64.rpm
dnf install oracle-ai-database-free-26ai-23.26.0-1.el8.x86_64.rpm

-- Step 3: Configure the database
/etc/init.d/oracle-free-26ai configure

‍

Configure environment variables and log in to the database

# Temporarily set environment variables (will not persist after logout)
[oracle@Jack ~]$ export ORACLE_SID=FREE
[oracle@Jack ~]$ export ORAENV_ASK=NO
[oracle@Jack ~]$ . /opt/oracle/product/26ai/dbhomeFree/bin/oraenv
The Oracle base has been set to /opt/oracle
[oracle@Jack ~]$ which sqlplus
/opt/oracle/product/26ai/dbhomeFree/bin/sqlplus
[oracle@Jack ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.0.0.0 - Production on Wed Oct 20 22:57:33 2025
Version 23.26.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

‍

Now, let's start introducing the new features.

​1. SELECT Statements Without FROM Clause​

You can now run queries containing only SELECT expressions without a FROM clause. This new feature improves the portability of SQL code and facilitates developer usage. No need for extra explanation, as shown in the figure below.

​2. IF [NOT] EXISTS Syntax Support​

DDL commands like CREATE, ALTER, and DROP for objects now support the IF EXISTS and IF NOT EXISTS syntax modifiers. This allows you to control whether an error should be raised if a given object exists or does not exist. If the check fails, the command is ignored without an error. The IF [NOT] EXISTS syntax can simplify error handling in scripts and applications.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/adfns/sql-processing-for-application-developers.html#GUID-3818B089-D99D-437C-862F-CBD276BDA3F1

Example:

DROP TABLE IF EXISTS <table_name>...

‍

If the table exists, it is dropped. If the table does not exist, the statement is ignored, so no error is raised. The same check mechanism exists when creating objects. Imagine a scenario where you don't have IF NOT EXISTS support. Before executing a query, you expect a certain table to exist, but if it doesn't, you must create a new table. You could use PL/SQL or query the data dictionary to determine if the table exists. If the table doesn't exist, you could execute dynamic SQL (EXECUTE IMMEDIATE) to create the table. With IF NOT EXISTS support, you can use the command CREATE TABLE IF NOT EXISTS <table_name>...to create the table if it doesn't exist. If a table with this name already exists, regardless of its structure, the statement is ignored without an error.

CREATE <object type> [IF NOT EXISTS] <rest of syntax>

-- create table if not exists
CREATE TABLE IF NOT EXISTS t1 (c1 number);
-- alter table if exists
ALTER TABLE IF EXISTS t1 ADD c2 number;
-- drop table if exists
DROP TABLE IF EXISTS t1;

‍

The IF NOT EXISTS clause is not allowed in CREATE OR REPLACE syntax.

Here are some examples of how CREATE OR REPLACE statements and CREATE statements can or cannot be used with the IF NOT EXISTS clause:

-- not allowed, REPLACE cannot coexist with IF NOT EXISTS
CREATE OR REPLACE SYNONYM IF NOT EXISTS t1_syn FOR t1;
-- allowed
CREATE SYNONYM IF NOT EXISTS t1_syn FOR t1;
-- allowed
CREATE OR REPLACE SYNONYM t1_syn FOR t1;

‍

3. New Database Role for Application Developers​

The DB_DEVELOPER_ROLE provides application developers with all the privileges needed to design, implement, debug, and deploy applications on the Oracle Database. By using this role, DBAs no longer need to guess which privileges application development might require; granting this one role satisfies all needs. This was mentioned back in 23c.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg/managing-security-for-application-developers.html#GUID-DCEEC563-4F6C-4B0A-9EB2-9F88CDF351D7

GRANT DB_DEVELOPER_ROLE TO Jack;
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='Jack';
REVOKE DB_DEVELOPER_ROLE FROM Jack;

‍

4. GROUP BY ALL​

In complex SQL queries with aggregate functions in the SELECT list, the new GROUP BY ALL clause eliminates the need to put all non-aggregated columns into the GROUP BY clause. Instead, the new ALL keyword indicates that the results should be automatically grouped by all non-aggregated columns. There's no need to repeat non-aggregated columns in the GROUP BY clause, making writing SQL queries faster and less error-prone. Users can use the GROUP BY ALL feature for quick prototyping of SQL queries or for ad-hoc queries.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html#SQLRF-GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

Notes:

• ALL is a reserved word, so it cannot be used as a column name or column alias.
• ALL cannot be used with other GROUP BY syntax options. If ALL is specified, then GROUP BY ALL is the only allowed group_by_clause syntax. Specifically, you cannot specify ROLLUP, CUBE, or GROUPING SETS with GROUP BY ALL.
• GROUP BY ALL includes all select list expressions except the following, which are not valid GROUP BY expressions: grouping functions or expressions containing grouping functions, scalar subqueries, window functions.
• GROUP BY ALL also excludes constants (including NULL) and bind select list expressions. The main reason for skipping constants is to avoid ambiguity when positional grouping is enabled.
• GROUP BY ALL does not extract parts of the select list expressions for GROUP BY: the entire expression is either included in the GROUP BY or not at all.
• GROUP BY ALL can be used in views and materialized views. The definition queries for both stored in the dictionary will contain GROUP BY ALL, not the transformed GROUP BY clause.
• Full text match rewrite is supported for materialized views with GROUP BY ALL, but partial text match rewrite is not.
• GROUP BY ALL can be used in WITH clause queries. It is supported anywhere a GROUP BY clause is allowed.
• HAVING conditions can be specified with GROUP BY ALL.
• GROUP BY ALL is not supported with the MODEL clause. If specified, error: "GROUP BY ALL not supported with MODEL clause".
• GROUP BY expression limits (1000 or 4k) apply to GROUP BY ALL. An error is raised if the limit is exceeded.
• GROUP BY ALL is not supported in the CREATE MATERIALIZED ZONE MAP DDL that defines a subquery. If specified, error: "zone map does not allow the construct or object GROUP BY ALL clause".

Example:

​5. GROUP BY Using Column Aliases or Positions​

In 26ai, you can now use column aliases or SELECT item positions in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases. These improvements make writing GROUP BY and HAVING clauses easier. They can make SQL queries more readable and maintainable while providing better SQL code portability.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2066419

Restrictions for the GROUP BY Clause: This clause is subject to the following restrictions:

• You cannot specify LOB columns, nested tables, or varrays as part of expr.
• Expressions can be of any form except scalar subquery expressions.
• If the grouping clause references any object type columns, the query will not be parallelized.
• To group by position, the parameter group_by_position_enabled must be set to true. Its default value is false.

Example:

SQL> select job, deptno, sum(sal) from emp group by 1,2;
select job, deptno, sum(sal) from emp group by 1,2
       *
ERROR at line 1:
ORA-03162: "JOB": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE
Help: https://docs.oracle.com/error-help/db/ora-03162/

-- Modify the parameter
alter system set group_by_position_enabled=true;

select job God_job, deptno, sum(sal) from emp group by God_job, 2;

SELECT manager_id, EXTRACT(YEAR FROM hire_date) AS hired_year, COUNT(*)
  FROM employees GROUP BY 1, 2;

SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY CUBE (department_name, job_id)
   ORDER BY department_name, job_id;

SELECT department_id, manager_id
   FROM employees
   GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN
   (SELECT department_id, manager_id FROM employees x
      WHERE x.department_id = employees.department_id)
   ORDER BY department_id;

‍

6. SQL Non-Positional INSERT Clause​

Oracle AI Database 26ai adds the INSERT INTO SET clause, a simpler, self-explanatory syntax for the INSERT INTO statement. The SET clause for INSERT INTO statements is the same as the existing SET clause in UPDATE statements. It also adds the BY NAME clause when inserting the results of a subquery. This clause matches source and target columns by name rather than by their position in the INSERT and SELECT lists. The benefit of the SET clause is that it makes it immediately clear which column each value corresponds to in the INSERT INTO statement, whereas for current INSERT INTO statements with hundreds of columns, figuring this out is non-obvious and cumbersome. Similarly, when loading from a subquery, matching the order of hundreds of columns in the INSERT and SELECT lists is clumsy. The BY NAME clause allows these lists to be in different orders, simplifying the process of writing the statement.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423__I2125362

Example 1: Single-row insert without parentheses

INSERT INTO employees SET
   employee_id = 210, last_name = 'Smith', email = 'ASMITH', hire_date = SYSDATE, job_id = 'AD_ASST';

‍

Example 2: Single-row insert with parentheses

INSERT INTO employees SET
   (employee_id = 211, last_name = 'SmithS', email = 'ASMITHS', hire_date = SYSDATE, job_id = 'AD_ASST');

‍

Example 3: Multi-row insert

INSERT INTO employees SET
   (employee_id = 212, last_name = 'SmithE', email = 'ASMITHE', hire_date = SYSDATE, job_id = 'AD_ASST'),
   (employee_id = 213, last_name = 'Roddick', email = 'ARODDICK', hire_date = SYSDATE, job_id = 'IT_PROG');

‍

nsert into ... by_name_position_clause supports non-positional insertion using a subquery, where the column names exposed in the subquery's select list (aliases or simple column names if no alias) are matched against the target table's column names to determine the order in which values should be inserted into the table. As a modifier for the subquery, the by_name_position_clause can optionally appear anywhere before the subquery.

The following two insert statements are semantically equivalent:

INSERT INTO job_history
  BY NAME
  SELECT employee_id, hire_date AS start_date, SYSDATE - 1 AS end_date, department_id, job_id FROM employees
  WHERE employee_id = 206;

INSERT INTO job_history (employee_id, start_date, end_date, department_id, job_id)
  SELECT employee_id, hire_date, SYSDATE - 1, department_id, job_id FROM employees
  WHERE employee_id = 206;

‍

The following examples (not new to 11g–26ai) insert data into multiple tables. Suppose you want to provide sales representatives with some information about orders of various sizes. The following example will create tables for small, medium, large, and special orders and populate them with data from the sample orders table

CREATE TABLE small_orders
   (order_id       NUMBER(12)   NOT NULL,
    customer_id    NUMBER(6)    NOT NULL,
    order_total    NUMBER(8,2),
    sales_rep_id   NUMBER(6)
   );
CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;

CREATE TABLE special_orders
   (order_id       NUMBER(12)    NOT NULL,
    customer_id    NUMBER(6)     NOT NULL,
    order_total    NUMBER(8,2),
    sales_rep_id   NUMBER(6),
    credit_limit   NUMBER(9,2),
    cust_email     VARCHAR2(40)
   );

-- INSERT ALL multi-table insert operation on the orders table:
INSERT ALL
   WHEN order_total <= 100000 THEN
      INTO small_orders
   WHEN order_total > 100000 AND order_total <= 200000 THEN
      INTO medium_orders
   WHEN order_total > 200000 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

‍

7. SQL Time Bucketing​

Time bucketing is a common operation when processing time-series or event-stream data, where a series of data points within arbitrarily defined time windows need to be mapped to specific fixed time intervals (buckets) for aggregate analysis. With the new SQL operator TIME_BUCKET, Oracle provides native and efficient time bucketing support for DATETIME-based data. Providing a native SQL operator for common fixed-interval time bucketing of time-series data significantly simplifies application development and data analysis for such information. In addition to making code simpler and less error-prone, the native operator can improve the performance of time-series analysis.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/dwhsg/sql-analysis-reporting-data-warehouses.html#GUID-91A27A23-A5E0-4CF7-8669-019146D22A4D

The TIME_BUCKET function supports bucketing input DATETIMES by a specified interval, aligned to a specific origin, and returns the start point of the bucket the time falls into. TIME_BUCKET directly supports the following input DATETIMES:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE
• NUMBER (EPOCH TIME)

The next three examples use the DATE data type. We first use Oracle INTERVAL syntax, then ISO 8601 syntax to select a five-year time window. The first two examples use the START parameter, which defines the start of the window as the return value. The third example uses the same statement but with the END parameter, which returns the end of the window.

SQL> select time_bucket(DATE '2022-06-29', 'P5Y', DATE '2000-01-01', START);
TIME_BUCKET(DATE'20
-------------------
2020-01-01 00:00:00

SQL> select time_bucket(DATE '2022-06-29', INTERVAL '5' YEAR, DATE '2000-01-01', START);
TIME_BUCKET(DATE'20
-------------------
2020-01-01 00:00:00

SQL> select time_bucket(DATE '2022-06-29', INTERVAL '5' YEAR, DATE '2000-01-01', END);
TIME_BUCKET(DATE'20
-------------------
2025-01-01 00:00:00

SQL> alter session set nls_date_format='dd mon syyyy hh24:mi:ss';
Session altered.

SQL> select time_bucket(TIMESTAMP '2022-06-29 12:34:56', INTERVAL '5' HOUR, TIMESTAMP '2022-06-29 00:00:00', END);
TIME_BUCKET(TIMESTAMP'2022-06-2912:34:56',INTERVAL'5'HOUR,TIMESTAMP'2022-06
---------------------------------------------------------------------------
29-JUN-22 03.00.00 PM

-- This is an example with CHAR data type. Note that the input is a character in NLS_TIMESTAMP_FORMAT and is automatically converted to TIMESTAMP. This is why the second parameter ORIGIN can be a timestamp.
SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
SQL> select time_bucket('2022-06-27 12:34:56', 'P5D', TIMESTAMP '2022-06-29 12:14:56');
TIME_BUCKET('2022-06-2712:34:56','P5D',TIMESTAMP'2022-06-2912:14:56')
---------------------------------------------------------------------------
2022-06-24 12:14:56

‍

8. SQL Generate 128-bit UUID​

A UUID is a 128-bit universally unique identifier widely used by applications to generate unpredictable random values that can be used as primary keys in tables, transaction IDs, or any form of unique identifier. In Oracle AI Database 26ai, the SQL function UUID() generates a version 4, variant 1 UUID in the database according to UUID RFC 9562. UUID generation and manipulation functions provide a compliant way to generate random, unique, and unpredictable identifiers that can be used to populate primary key columns in database tables, uniquely identify transaction IDs (e.g., for the sessionless transaction feature in Oracle AI Database 26ai), and many other uses. Modern applications expect to be able to generate unpredictable and random UUIDs. All major databases and data management systems support some form of UUID generation and manipulation. The current Oracle SQL operator SYS_GUID() always generates a predictable sequence of unique identifiers, which is not optimal.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/uuid.html#SQLRF-GUID-2A0ECCC2-3DA1-442F-AC9D-A6FE643F381D

UUID returns a version 4, variant 1 UUID as a RAW(16) value, formatted as: xxxxxxxx-xxxx-4xxx-Bxxx-xxxxxxxxxxxx, where x is a hexadecimal digit. UUID can optionally accept a version specifier of type NUMBER as input. UUID(0) and UUID(4) are equivalent to UUID(), as in both cases a version 4, variant 1 UUID is returned. Versions other than 4 and 0 return an error.

SELECT UUID();
UUID()
--------------------------------
4EC3D31B77224FEABF38456CE010BD90

select SYS_GUID();
SYS_GUID()
--------------------------------
41A302077A294D19E0638ED8000C623C

-- Note: RAW(16) has been converted to printable form
SQL> ALTER TABLE locations ADD (uid_col RAW(16));
SQL> UPDATE locations SET uid_col = UUID();
23 rows updated.

SQL> SELECT location_id, uid_col FROM locations
  2     ORDER BY location_id, uid_col;
LOCATION_ID UID_COL
----------- --------------------------------
       1000 3E74D833847A4F7EBF56CEFD3936BCF8
       1100 5F62472A5A414FBCBFB81298098E9BE9
       1200 0768F8460D324F3ABF551C0728E27A96
       1300 6499D717251C4F14BF34E3E038186A3E

‍

​9. Support for Boolean Data Type​

Oracle 23c introduced the SQL Boolean data type. This was introduced even before the 23c release, so let's briefly discuss it here. The Boolean data type has truth values TRUE and FALSE. Without a NOT NULL constraint, the Boolean data type also supports the truth value UNKNOWN as NULL. In SQL syntax, the Boolean data type can be used anywhere a data type is expected.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Data-Types.html#GUID-285FFCA8-390D-4FA9-9A51-47B84EF5F83A

For example, in a CREATE TABLE statement, you can use the keywords BOOLEAN or BOOL to specify a Boolean column:

CREATE TABLE example (id NUMBER, c1 BOOLEAN, c2 BOOL);

‍

You can use the SQL keywords TRUE, FALSE, and NULL to represent the states "TRUE", "FALSE", and "NULL" respectively. For example, using the table created above:

INSERT INTO example VALUES (1, TRUE, NULL);
INSERT INTO example VALUES (2, FALSE, true);
INSERT INTO example VALUES (3, 0, 'off');
INSERT INTO example VALUES (4, 'no', 'yes');
INSERT INTO example VALUES (5, 'f', 't' );
INSERT INTO example VALUES (6, false, true);
INSERT INTO example VALUES (7, 'on', 'off');
INSERT INTO example VALUES (8, -3.14, 1);

‍

Note that numbers are converted to Boolean as follows:

• 0 converts to FALSE.
• Non-zero values, such as 42 or -3.14, convert to TRUE.

Note that comparison operators are supported to compare Boolean values.

SELECT * FROM example WHERE c1 = c2;
SELECT * FROM example e1
WHERE c1 >= ALL (SELECT c2 FROM example e2 WHERE e2.id > e1.id);
SELECT * FROM example WHERE NOT c2;
SELECT * FROM example WHERE c1 AND c2;
SELECT * FROM example WHERE c1 AND TRUE;
SELECT * FROM example WHERE c1 OR c2;

‍

10. DEFAULT ON NULL Handling for UPDATE Statements​

You can now define a column to use DEFAULT ON NULL for update operations, whereas previously this was only possible for insert operations. When an update operation attempts to update a value to NULL, a column specified as DEFAULT ON NULL is automatically updated to a specific default value. This feature simplifies application development by eliminating the need for complex application code or database triggers to achieve the desired behavior. Development efficiency is improved, and the code is less error-prone.

Official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__CJAIGEJE

Example:

The following statement creates a table named myemp that can be used to store employee data. The department_id column is defined with a default value of 50 (when the value is NULL). Therefore, if a subsequent INSERT statement attempts to assign NULL to department_id, 50 is assigned instead.

SQL> conn jack/Pass_word123********

CREATE TABLE myemp (employee_id number, last_name varchar2(25), department_id NUMBER DEFAULT ON NULL 50 NOT NULL);

-- In the employees table,

‍