Monday, July 7, 2025

Oracle 23ai New Features.

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