Oracle 23c/23ai Database New Features

Oracle 23c is the latest Release of the Oracle Database. This is also a Long term release. It is also called Oracle 23ai given the focus on AI in it. Oracle has introduced a lot of new features in it. Here are the Top Features

Table of Contents

Augmenting a new generation of AI models

Oracle Database 23ai has introduced AI Vector Search, a powerful new technology that enables users to leverage a new generation of AI models to generate and store vectors.
These vectors, sometimes referred to as embeddings, are multi-dimensional representations of documents, images, videos, sound, etc.
By encoding these objects as vectors, the User gains the ability to look for similarities between them using mathematical calculations.

The real power of Oracle Database23ai’s solution is that you can combine these similarity searches with searches on your business data using simple SQL. Anyone with a basic understanding of SQL can create a powerful statement combining similarity and other search criteria.
These types of queries provide LLMs with additional context, augmenting their knowledge and making their responses more accurate and relevant to your customers’ or organizations’ questions. To enable this functionality, we’ve added a new data type, new vector indexes, and extensions to the SQL language to make it incredibly simple to query vectors alongside your existing business data by leveraging Oracle Database 23ai’s advanced analytical capabilities.

Asking questions naturally

While SQL is an incredibly powerful language, it can be a little challenging to get started with. To enable a broader range of users to ask the most complex questions of the Oracle Database, we are integrating with LLMs such as Cohere and Llama to allow you to ask questions using natural language. So you can simply ask a question like “Show me the last 4 quarters’ sales of products that are popular with the younger generation”. Oracle Database 23ai will share the table’s metadata to answer the question with the LLM along with the user’s question. The LLM understands the meaning of “younger generation” and converts it into a data range as a filter in the SQL query, which it returns to the database. We, of course, aren’t limiting Oracle Database 23ai to just allowing you to query the information within the database. You’ll also have the ability to ask questions on all aspects of the Oracle Database.

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 (Removal of the need for “from dual” in simple select statements)

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 support 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 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, which 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.