Oracle 23c Database New Features

Oracle 23c is the latest Release of Oracle Database. Oracle has introduced lot of new features in it. Here are the Top Features

Developer-Friendly Features

Oracle has been adding features to make developers’ lives easier, such as SQL and PL/SQL enhancements, JSON improvements, REST interfaces, etc.

SELECT without FROM clause

You can now run SELECT expression-only queries without a FROM clause. This new feature improves SQL code portability and ease of use for developers

Example
select sysdate;

IF [NOT] EXISTS

DDL object creation, modification, and deletion in Oracle Database now supports the IF EXISTS and IF NOT EXISTS syntax modifiers. This enables you to control whether an error should be raised if a given object exists or does not exist, simplifying error handling in scripts and by applications.

create table if not exists test (id number);
drop table IF EXIST test;

Annotations in Oracle Database 23c

This gives the name-value pair for the database objects

Key value pair annotations for (some) objects provide metadata for data and schema.

create table test (id number )
annotations (
release_version  'R1.1',
Description  'This is test'
);

Annotations are supported for tables, views, materialized views, columns, indexes and domains.
Annotations can be queried in the data dictionary:

select * from user_annotations;
select * from user_annotation_values;
select * from user_annotations_usage;

New Boolean Datatype

• Oracle Database now supports the ISO SQL standard-compliant Boolean data type.
• This enables you to store True and False values in tables and use Boolean expressions in SQL statements. The Boolean data type standardizes the storage of Yes and No values and makes it easier to migrate to Oracle Database

Direct Joins for UPDATE and DELETE Statements

• Oracle Database now allows you to join the target table in UPDATE and DELETE statements to other tables using the FROM clause.
• These other tables can limit the rows that are changed or be the source of new values. Direct joins make it easier to write SQL to change and delete data.

SQL Macro

• Create SQL macros to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements.
• SQL macros can be scalar expressions that are typically used in SELECT lists as well as WHERE, GROUP BY, and HAVING clauses. SQL macros can also be used to encapsulate calculations and business logic or can be table expressions, typically used in a FROM clause

create or replace function top_row (
tab dbms_tf.table_t, num_rows number
) return varchar2 sql_macro is
begin
return 'select * from top_n.tab
fetch first top_n.num_rows
rows only';
end top_row;
select * from top_row(test, 5);

Advanced Security Features

Security is always a top priority, so new or enhanced security mechanisms and features are added.

Schema Privileges to Simplify Access Control

• Oracle Database now supports schema privileges in addition to existing object, system, and administrative privileges.
• This feature improves security by simplifying authorization for database objects to better implement the principle of least privilege and keep the guesswork out of who should have access to what.

--For Sequence
grant select any sequence on schema HR to APPS;
-- Tables, views, materialized views
grant select any table on schema HR to APPS;
grant insert any table on schema HR to APPS;

TLS 1.3

Starting with Oracle Database 23c, Oracle Database supports Transport Layer Security (TLS) version 1.3, which affects the use of cipher suites in TLS settings.

ORACLE_DEVELOPER role

• Oracle Database 23c includes the new role DB_DEVELOPER_ROLE, which provides an application developer with all the necessary privileges to design, implement, debug, and deploy applications on Oracle Databases.
• By using this role, administrators no longer have to guess which privileges may be necessary for application development.

Converged Database Capabilities

Oracle has been emphasizing the convergence of various data models and types into a single database. New features that simplify working with JSON, Graph, XML, and other data models are included

Operational property graphs

• Oracle Database offers native support for property graph data structures and graph queries. If you’re looking for flexibility to build graphs in conjunction with transactional data, JSON, Spatial, and other data types, we got you covered.
• Developers can now easily build graph applications with SQL using existing SQL development tools and frameworks.

JSON Relational Duality

• It is an innovation introduced in Oracle Database 23c, unifies the relational and document data models to provide the best of both worlds.
• Developers can build applications in either relational or JSON paradigms with a single source of truth and benefit from the strengths of both models.
• Data is held once but can be accessed, written, and modified with either approach. Developers benefit from ACID-compliant transactions and concurrency controls, which means they no longer have to make trade-offs between complex object-relational mappings or data inconsistency issues.