Note:
- This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
- It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.
Explore SQL Features in Oracle Database 23ai
Introduction
We will learn 10 features in this tutorial that you need to know and how they compare to their existing counterparts. These features are:
FROM
clause (optional).BOOLEAN
for SQL.IF NOT EXISTS
Data Definition Language (DDL) clause.INSERT
multivalues.- New table
VALUE
constructor. - Aliases in
GROUP BY
clause. RETURNING
clause ofUPDATE
andMERGE
statement.- Joins in
UPDATE
andDELETE
. - Annotations, a new metadata for database objects.
- Lightweight object types with SQL domains.
Objectives
-
Use SQL Without the
FROM
Clause: Enable simpler queries by removing the requirement to use theFROM
clause when selecting expressions or built-in functions. -
Implement Native
BOOLEAN
Data Type in SQL: Leverage the nativeBOOLEAN
data type in SQL tables, queries, and conditions for more intuitive true/false logic handling. -
Utilize
IF NOT EXISTS
in DDL Statements: Simplify object creation and deletion logic by conditionally executingCREATE
andDROP
statements without additional PL/SQL checks. -
Perform Multivalue
INSERT
Operations: Insert multiple rows in a singleINSERT
statement to improve code readability and reduce round-trips to the database. -
Use Table Value Constructor for Inline Data Sets: Create temporary row sets directly in SQL using the
VALUES
constructor, supporting operations likeMERGE
,SELECT
, or comparison. -
Reference Column Aliases in GROUP BY Clauses: Enhance query readability by allowing the use of
SELECT
aliases inGROUP BY
instead of repeating expressions. -
Leverage
RETURNING
Clause inUPDATE
andMERGE
: Retrieve affected data directly fromUPDATE
andMERGE
statements without needing a follow-up query. -
Perform Joins in
UPDATE
andDELETE
Statements: UseJOIN
logic directly inUPDATE
andDELETE
operations to modify or remove records based on related table conditions. -
Annotate Database Objects with Metadata: Document database objects using
ANNOTATION
to store descriptive metadata (for example, owner, purpose) for easier maintenance and introspection. -
Define Lightweight Object Types Using SQL Domains: Create reusable domain types with constraints to enforce consistency and strong typing across multiple tables.
Prerequisites
-
Basic SQL knowledge.
-
Understand SQL syntax:
SELECT
,INSERT
,UPDATE
,DELETE
,JOIN
,GROUP BY
and so on. -
Familiarity with relational database concepts and data types.
-
-
Experience with Oracle Database 23ai and its prior versions.
-
Awareness of how DDL, Data Manipulation Language (DML), and PL/SQL worked in Oracle Database 19c, Oracle Database 21c and earlier versions.
-
Knowledge of Oracle-specific features like
DUAL
,MERGE
,RETURNING INTO
and so on.
-
-
Access to Oracle Database 23ai environment.
-
Have access to Oracle Database 23ai (local setup, cloud instance, or Oracle Live SQL).
-
Some features (like SQL domains or
BOOLEAN
) only work in Oracle Database 23ai.
-
-
SQL*Plus, SQLcl, or GUI Tools (like SQL Developer or DataGrip). Able to run and test SQL statements in a compatible interface.
-
PL/SQL fundamentals (for advanced features). For using
RETURNING INTO
, procedural blocks, and handling dynamic SQL. -
Knowledge of constraints and data integrity rules. Needed to understand SQL domains and table constraints.
-
Familiarity with Oracle data dictionary views. For querying annotations or metadata. For example,
USER_TABLES
,USER_ANNOTATIONS
. -
Role and privileges in Oracle Database. Ability to create/alter tables, domains, and annotations—requires appropriate user privileges.
-
Version awareness. Ensure your tools and clients support Oracle Database 23ai features (older drivers or tools may fail).
-
(Optional) Exposure to other modern SQL dialects (PostgreSQL, MySQL, and so on). This will help you to appreciate cross-compatibility of new features like
VALUES
,BOOLEAN
, andIF EXISTS
.
Feature 1: Use FROM
Clause
An interesting feature introduced in Oracle Database 23ai is optionality of FROM
clause in SELECT
statements. Up to this version the FROM
clause was required.
Here are some potential benefits of a SELECT
without FROM
feature in Oracle Database 23ai.
-
Select current date for easier data manipulation.
SELECT CURRENT_DATE;
-
Mathematical operations or calculations without involving table data.
SELECT 25.50*25.25; 25.50*25.25 ----------- 643.875 Elapsed: 00:00:00.002 1 rows selected.
-
PL/SQL block without using
FROM
clause.CREATE SEQUENCE empno_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 1000; Sequence EMPNO_SEQ created. Elapsed: 00:00:00.005 declare v1 number; begin select empno_seq.nextval into v1; dbms_output.put_line ('v1= '||v1); end; / v1= 1 PL/SQL procedure successfully completed. Elapsed: 00:00:00.009
-
Built-in or user defined functions to perform operations or retrieve values.
SELECT DBMS_RANDOM.VALUE() as random_number;
-
String manipulations or conversions without relying on table data.
SELECT UPPER('oracle') AS uppercase_text;
-
Conditional or logical expressions without using a table.
SELECT CASE WHEN 10 > 5 THEN 'True' ELSE 'False' END AS result;
Feature 2: Use BOOLEAN
Datatype
Oracle Database 23ai introduces the new BOOLEAN
data type. This leverages the use of true boolean columns/variables, instead of simulating them with a numeric value or Varchar. The ability to write boolean predicates simplifies the syntax of SQL statements.
-
Create a table called
TEST_BOOLEAN
.CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN); Table TEST_BOOLEAN created. Elapsed: 00:00:00.004
-
Enter data into the new table. The value
IS_SLEEPING
will beNOT NULL
set toFALSE
as default.ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);
ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE); Table TEST_BOOLEAN altered. Elapsed: 00:00:00.014
Here, you can see the different types of boolean input for Mick, Keith, and Ron. All are valid. For Mick, the default FALSE value is used - Mick is not sleeping.
1 row inserted. Elapsed: 00:00:00.006
For Keith, we use a NO value - Keith is not sleeping.
INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO'); 1 row inserted. Elapsed: 00:00:00.002
And for Ron we use a 1 value - Ron is sleeping.
-
See some results based on our boolean values.
SELECT * FROM test_boolean;
You no longer need to remember what kind of boolean system you put in place. As we have shown, using 0/1, True/False, Yes/No, or any other common inputs will return an accurate table value.
Feature 3: Use IF NOT EXISTS
DDL Clause
Starting with Oracle Database 23ai, the new IF NOT EXISTS
DDL clause allows to decide how DDL errors will be handled. This simplifies the DDL scripting, as potential errors due to objects existence or inexistence can be hidden to the scripting.
-
First, test without using this new feature. Run the following statement.
DROP TABLE DEPT;
Since there is no existing
DEPT
table to drop, we will see an error:ORA-00942: table or view does not exist
. -
However, in Oracle Database 23ai, we are able to use
DROP IF EXISTS
without an error. This gives us peace of mind while avoiding errors. Now, run the same statement, but include this newIF EXISTS
feature.DROP TABLE IF EXISTS DEPT;
-
Similarly, we can use this feature to create tables, if they do not already exist. Create that
DEPT
table.CREATE TABLE IF NOT EXISTS DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ;
-
Use this feature to create more sample tables throughout this tutorial. Here, we will make an employee table called
EMP
.CREATE TABLE IF NOT EXISTS EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); Table EMP created. Elapsed: 00:00:00.006
Feature 4: Use INSERT
for Multiple Rows
Another interesting feature ensuring better coexistence and compatibility with other frequently used database management systems is multivalue INSERT
statement.
-
In previous versions of Oracle database, as an example, inserting multiple rows required a separate insert statement for each row.
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES'); INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO'); INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');
Oracle Database 23ai introduced the new syntax allowing for inserting all these rows in a single one
INSERT
statement, so you may insert several tuples in one DML. Run the following statement.INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'), (50,'HR','LOS ANGELES'), (60,'IT','SAN FRANCISCO'), (70,'MANUFACTURING','DETROIT');
In addition to better compatibility with other databases, this statement can be used to ensure consistency of some insert operations in auto-commit mode. This could be important, for example, for Oracle APEX applications using this mode to work on data.
-
Run the following statement to use this feature to fill in values for the
EMP
table.INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20), (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30), (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30), (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30), (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30), (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10), (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20), (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10), (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30), (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20), (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30), (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20), (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
Feature 5: Use New VALUE
Constructor
Starting from Oracle database 23ai, table values constructor has been extended. It can now be used in INSERT
statements, to create several rows in a single command. It can also be used in SELECT
statements, and in the view factorization syntax. In this last case, it simplifies the syntax of the statements, and avoids using the DUAL
table.
The following statement looks like a kind of table function on the fly.
SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);
Feature 6: Use Aliases in GROUP BY
Clause
Oracle Database 23ai introduces the ability to use aliases in GROUP BY
clause of a SELECT
statement. This feature simplifies writing queries with complex expressions as well as ensures better compatibility with some other relational databases, like Teradata, MySQL and PostgreSQL.
For example:
SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');
In Oracle Database 23ai this can be written in a simpler way as shown in the following image:
Feature 7: Use RETURNING
Clause of UPDATE
and MERGE
Statement
This clause had been implemented earlier as a part of the EXECUTE IMMEDIATE
statement. However in Oracle Database 23ai we can find it as a part of traditional, static DML statements.
-
In this case, it allows for obtaining old and new values of columns from a row processed. First, let us see King’s current salary.
SELECT ename, sal FROM emp WHERE ename = 'KING';
-
To use variables in LiveSQL, we will wrap out statements in PL/SQL. Run this script. It will first create the variables for old and new salary, then update King’s salary using the
RETURNING
clause to set our variables. We will then take a look at the results.BEGIN DECLARE old_salary NUMBER; new_salary NUMBER; BEGIN UPDATE emp SET sal = sal + 1000 WHERE ename = 'KING' RETURNING OLD sal, NEW sal INTO old_salary, new_salary; DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary); DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary); END; END;
Old Salary: 6000 New Salary: 7000
This example used an
UPDATE
statement, but theRETURNING
clause can be used similarly withMERGE
statements.
Feature 8: Use Joins in UPDATE
and DELETE
You may update table data using joins based on foreign table conditions. There is no need for sub selects or IN
clause.
-
Run the following statement to see the employee salary information from the research department.
select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';
-
To update the salary information, prior to Oracle Database 23ai we need to use a nested statement.
UPDATE emp e set e.sal=e.sal*2 WHERE e.deptno in (SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno and d.dname='RESEARCH');
With Oracle Database 23ai, you can use it like this:
UPDATE emp e set e.sal=e.sal*2 FROM dept d WHERE e.deptno=d.deptno and d.dname='RESEARCH';
-
You can see the salary has been successfully updated.
select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';
Feature 9: Use Annotations
Annotations are optional metadata for database objects. An annotation is either a name-value pair or a name by itself. The name and optional value are freeform text fields. An annotation is represented as a subordinate element to the database object to which the annotation has been added. Supported schema objects include tables, views, materialized views, and indexes. With annotations you may store and retrieve metadata about a database objects. You can use it to customize business logic, user interfaces or provide metadata to metadata repositories. It can be added with CREATE
or ALTER
statement on table or column level.
With annotations you may store and retrieve metadata about database objects. You can use it to customize business logic, user interfaces or provide metadata to metadata repositories.
-
Create an annotated table
EMP_ANNOTATED_NEW
with column and table annotations.CREATE TABLE emp_annotated_new (empno number annotations(identity, display 'person_identity', details 'person_info'), ename varchar2(50), salary number annotations (display 'person_salary', col_hidden)) annotations (display 'employee_table');
-
Data dictionary views such as
USER_ANNOTATIONS
andUSER_ANNOTATIONS_USAGE
can help to monitor the usage.SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;
Feature 10: Use SQL Domains
A SQL domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints and is created with a CREATE DOMAIN
statement. Domains provide constraints, display, ordering and annotations attributes. After you define a SQL domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain’s optional properties and constraints to those columns.
SQL domains allow users to declare the intended usage for columns. They are data dictionary objects so that abstract domain specific knowledge can be easily reused.
-
Create a domain named
yearbirth
and table namedperson
.CREATE DOMAIN yearbirth as number(4) constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900)) display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100) order (yearbirth -1900) annotations (title 'yearformat');
CREATE TABLE person (id number(5), name varchar2(50), salary number, person_birth number(4) DOMAIN yearbirth ) annotations (display 'person_table');
``` desc person;
Name Null? Type
ID NUMBER(5) NAME VARCHAR2(50) SALARY NUMBER PERSON_BIRTH NUMBER(4) DOMAIN YEARBIRTH
INSERT INTO person values (1,’MARTIN’,3000, 1988);
2. With the new function `DOMAIN_DISPLAY` you can display the property.
SELECT DOMAIN_DISPLAY(person_birth) FROM person;

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.
SELECT * FROM user_annotations_usage;
Define reusable domain types (lightweight objects).
CREATE DOMAIN EmailAddress AS VARCHAR2(100) CHECK (REGEXP_LIKE(VALUE, ‘^[^@]+@[^@]+.[^@]+$’));
CREATE TABLE users ( user_id NUMBER, email EmailAddress ); ```
Limitations and Restrictions in Oracle Database 23ai Features
-
FROM
Clause.-
Only applicable to simple expressions like functions, literals, or variables.
-
Cannot be used with queries involving tables, joins, or subqueries.
-
Not supported in PL/SQL context with cursor loops expecting a
FROM
clause.
-
-
Native
BOOLEAN
Data Type.-
Can be used in table columns and expressions.
-
Not indexable, columns with
BOOLEAN
data type cannot be indexed. -
Not directly supported in all client tools or reporting tools (may need translation to 0/1 or Y/N).
-
Not supported in some older APIs or drivers (JDBC/ODBC clients may need updates).
-
-
IF NOT EXISTS
in DDL.-
Simplifies idempotent DDL scripts.
-
Only available for specific objects:
TABLE
,INDEX
,SEQUENCE
,VIEW
, and so on. -
Not all object types support this (for example,
TRIGGER
,SYNONYM
may still require manual checking). -
Not supported in older Oracle versions.
-
-
Multivalue
INSERT
s.-
Clean syntax for batch inserts.
-
Limited to explicit value sets cannot insert using
SELECT
or subqueries in the sameVALUES
clause. -
Cannot be combined with
RETURNING
clause for all inserted rows in one step.
-
-
Table Value Constructor (
VALUES
Clause).-
Ideal for small, ad-hoc row sets.
-
Has row limits (typically 999 or fewer depending on the context).
-
Not optimized for large-scale loads, better to use temporary tables or staging for large datasets.
-
-
Aliases in
GROUP BY
Clause.-
Makes queries easier to read and write.
-
Not supported in all analytic functions or complex queries involving subquery columns.
-
Might cause confusion in queries where alias is overloaded (for example, same name in inner query).
-
-
RETURNING
Clause inUPDATE
/MERGE
.-
Reduces need for
SELECT
after DML. -
Can only return values from rows actually modified.
-
Not usable for bulk updates without
FORALL
, must use PL/SQL with explicitRETURNING BULK COLLECT
.
-
-
Joins in
UPDATE
andDELETE
.-
Makes multi-table logic simpler.
-
Only
INNER JOIN
andLEFT JOIN
types supported in some contexts. -
May require rewrite for
MERGE
logic if using older tools or needing compatibility with earlier Oracle versions.
-
-
Annotations.
-
Great for documentation.
-
Annotations are metadata only, not enforceable at runtime.
-
Requires use of Oracle data dictionary views (
*_ANNOTATIONS
) to retrieve. -
Not integrated into Oracle Data Pump export/import or replication tools yet.
-
-
Lightweight Object Types with SQL Domains.
-
Reusable, consistent type enforcement.
-
Still less powerful than full object types (no methods, attributes).
-
No inheritance or composition, meant for primitive constraint enforcement only.
-
Cannot define default values in domain itself (defaults still specified at column level).
-
General Restrictions
-
Tool Compatibility: Many GUI tools and older Oracle client libraries may not yet fully support Oracle Database 23ai syntax (especially
VALUES
,BOOLEAN
,DOMAIN
). -
Export/Import: Some features like annotations or domains may not be preserved in older
expdp
/impdp
workflows. -
Experimental Behaviour: As these features are new, some may evolve in minor releases — check patch notes and documentation frequently.
Related Links
Acknowledgments
- Author - Aditya Srivastawa (Principal Cloud Architect)
More Learning Resources
Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.
For product documentation, visit Oracle Help Center.
Explore SQL Features in Oracle Database 23ai
G35689-01
Copyright ©2025, Oracle and/or its affiliates.