There are a few new features introduced in Oracle 23ai that have been in other RDBMS systems for a while.
These features enable compatibility with other systems and also make it easier for non-Oracle database developers.
I welcome these features,
1) as it opens to all DB developers
2) and it lead to simpler / Readable SQL
Here we go ...
a) No need for DUAL
There is no need to use DUAL for SELECT that uses only expression.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
SQL> select sysdate ;
SYSDATE
---------
07-JUL-25
SQL> select level connect by level <=5;
LEVEL
----------
1
2
3
4
5
b) Boolean Datatype
The new datatype 'boolean' simplifies the development.
Now, it can hold boolean values ( true/false, 1 /0 )
Here is an example.
SQL> create table t ( c1 int , c2 boolean) ;
Table created.
SQL> insert into t values ( 1 , 'true');
1 row created.
SQL> insert into t values ( 2 , 'false');
1 row created.
SQL> insert into t values ( 3 , 1);
1 row created.
SQL> select * from t;
C1 C2
---------- -----------
1 TRUE
2 FALSE
3 TRUE
C) VALUES clause
Continuing the example from "Boolean Datatype",
We can replace those 3 individual insert SQLs with one SQL as below.
SQL> delete from t;
3 rows deleted.
SQL> insert into t values ( 1 , 'true') , ( 2 , 'false'),( 3 , 1);
3 rows created.
SQL> select * from t;
C1 C2
---------- -----------
1 TRUE
2 FALSE
3 TRUE
c) Simplified Update / Delete
The UPDATE / DELETE are handled easily with the new syntax.
As an example, we need to increase the salaries of all employees in Location ID "1700" to receive 2.25%, then we can use the simple join to accomplish it.
SQL> select * from t_dept;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
SQL> Update t_emp e
set e.salary = e.salary + ( e.salary * ( 2.25 / 100 ) )
from t_dept d
Where e.department_id = d.department_id and d.location_id = 1700;
18 rows updated.
e) "RETURNING" Clause
We can use the "RETURNING" clause to identify the state and the future state of the impacted records.
This can be used in SELECT , INSERT , UPDATE , DELETE and MERGE statements.
In the following example, the salary of the employee with ID 111 is given a 2.25% increase.
SQL> select employee_id , salary from t_emp where employee_id = 111;
EMPLOYEE_ID SALARY
----------- ----------
111 7700
SQL> var l_proposed_sal number;
SQL> var l_current_sal number;
SQL> Update t_emp
set salary = salary + ( salary * ( 2.25 / 100 ) )
Where employee_id = 111
Returning new salary ,old salary
into :l_proposed_sal , :l_current_sal ;
1 row updated.
SQL> print :l_proposed_sal ;
L_PROPOSED_SAL
--------------
7873.25
SQL> print :l_current_sal ;
L_CURRENT_SAL
-------------
7700
