Oracle Database 23c: 10 New Magic Features

Hello,
Oracle announced the new long term release of 23c Beta. In this blog post, we will discuss some Oracle Database 23c New magic features.

1- The DUAL table is not needed anymore.

select ‘Talip Hakan’;
select 9+8 ;
select sysdate;

2-Using the new IF EXISTS and IF NOT EXIST(S?) clauses in DDL statements.

drop table IF EXIST DBMASTER_TEST;
create table IF NOT EXIST DBMASTER_TEST (id number, name varchar2(10));

3- Using the column alias/position number in GROUP BY and HAVING.

select count(*) as amount, upper(d.dname) as department_name
from dept d join emp e on d.deptno = e.deptno
group by upper(d.dname)
having count(*) > 3;

Now, you can write above query as below.

select count(*) as amount, upper(d.dname) as department_name
from dept d join emp e on d.deptno = e.deptno
group by department_name
having amount > 3;

From Oracle 23c, Also we can make use of the column position in the GROUP BY clause.

alter session set group_by_position_enabled=true;

select count(*) as amount, upper(d.dname) as department_name
from dept d join emp e on d.deptno = e.deptno
group by 2
having amount > 3;

4- You can give schema Level Privileges

grant select any table on schema HR to hakan;

5- You can allow to group multiple rows of data in a single DML or SELECT statement.

select *
from (values
(10, ‘test’, ‘test1’),
(20, ‘test’, ‘test2’),
(30, ‘test’, ‘test3’)
) a (id, txt, desc);

Using With Clause;

with a (id, txt, desc) AS (
values (10, ‘test’, ‘test1’),
(20, ‘test’, ‘test2’),
(30, ‘test’, ‘test3’)
)
select * from a;

Using MERGE Statement;

merge into DBMTEST a
using (values
(10, ‘test’, ‘test1’),
(20, ‘test’, ‘test2’),
(30, ‘test’, ‘test3’)
) b (id, txt, desc)
on (a.id = b.id)
when matched then
update set a.txt= b.txt,
a.desc= b.desc
when not matched then
insert (a.id, a.txt, a.desc)
values (b.id, b.txt, b.desc);

Using INSERT Statement;

insert into DBMTEST
values (10, ‘test’, ‘test1’),
(20, ‘test’, ‘test2’),
(30, ‘test’, ‘test3’);
commit;

6- Give to user necessary privileges for doing database development.

grant db_developer_role to talip;
revoke db_developer_role from talip;

begin
dbms_developer_admin.grant_privs(‘talip’);
end;
/

Note that the db_developer_role is created in $ORACLE_HOME/rdbms/admin/catdevrol.sql

7- Oracle 23c introduced the MAX_COLUMNS initialization parameter allows us to have up to 4096 columns in a table. Up to 4096 columns per table (requires max_columns to be set to extended and compatibility to be set to 23.0.0)

show parameter max_column
alter system set max_columns=EXTENDED scope=spfile;

When this parameter is set to STANDARD, the maximum number of columns allowed in a database table or view is 1000. When this parameter is set to EXTENDED, the maximum number of columns allowed in a database table or view is 4096. You can change the value of MAX_COLUMNS from STANDARD to EXTENDED at any time. However, you can change the value of MAX_COLUMNS from EXTENDED to STANDARD only when all tables and views in the database have 1000 or fewer columns.

8-Oracle database 23c introduced the boolean data type for SQL. The datatype can be bool or boolean

create table dbmaster_test (val varchar2(10), flag boolean);
insert into dbmaster_test values (‘true’ , true );
insert into dbmaster_test values (‘false’ , false);
insert into dbmaster_test values (‘null’ , null );
insert into dbmaster_test values (‘t’ ,’t’ );
insert into dbmaster_test values (‘yes’ ,’yes’ );
insert into dbmaster_test values (‘true’ ,’true’);
insert into dbmaster_test values (‘f’ ,’f’ );
insert into dbmaster_test values (‘0’ , 0 );

select val from dbmaster_test where flag ;

select val from dbmaster_test where flag is true ;
select val from dbmaster_test where not flag ;

9-Using direct joins to tables to drive UPDATE and DELETE statements.

update dbmaster_test1 a
set a.txt= b.txt,
a.desc = b.desc
from dbmaster_test2 b
where a.id = b.id
and b.id <= 5;

delete dbmaster_test1 a
from dbmaster_test2 b
where a.id = b.id
and b.id <= 5;

10-Oracle 23c free developer-release can be installed on Oracle Linux using an rpm package. We will discuss it on another blog post.