Oracle Database 23c–new feature highlights (release February 2023?)

Based on the fascinating Twitter thread by Philipp Salvisberg covering the presentation by Gerald Venzl at DOAG 2022 I would like to mention a few of the features that apparently will be part of Oracle Database 23c – the new Long Term Support release (succeeding 19c) that has a beta period starting in October 2022 and ending in February 2023. Very shortly we will be able to start playing with these features and before too long we can start using them for real. Note that Oracle Database 23c will be supported for a long time (well into 2028 and with extended support already projected into 2031).

Some new features always seemed so obvious but never happened. Until now.

1. Select without from – if what you want to select does not come from a table, you do not need to pretend that it does by using DUAL.

okay, this one will not keep you awake,  but it is nice, yes?

2. Use column alias in GROUP BY and HAVING – a long running frustration: if you want to group by a column expression, you cannot simply use the column alias (as you can in the ORDER BY) but you have to type (no typos please) the same column expression. In 23c we will be able to simply use the column alias!

3. Test for object existence in DDL statements using IF EXISTS and IF NOT EXIST(S?) – it is not elegant when your DDL statement are executed with what appear to be errors – but may be harmless – when the object you try to drop does not exist (which is great) or the object you try to create is already around (which may also be fine). Using the new IF EXISTS and IF NOT EXIST(S?) clauses in DDL statements you can cater in advanced for those situations.

4. Table Value Constructor – allows to group multiple rows of data in a single DML or SELECT statement.

This operator is part of the ISO SQL standard. It seems related to the TABLE function  – except that it contains not a function call but hard coded values for multiple rows. See for more details this article on TVC (in SQL Server)

5. Returning Clause for MERGE statement and also returning OLD column value for UPDATE

After performing a DML statement, the final result can be returned to the client (for example a PL/SQL client). However, until now RETURNING was not available for MERGE and it only returns the new value after doing an UPDATE. As of 23c, we can use the OLD column_name expression to get hold of the original column’s value.

6. Better error messages to explain why a statement failed to execute

I do not know how many messages have been improved. The example is at least a valuable one. The guy in the picture is Gerald Venzl, product manager at Oracle and very nice person.

7. Annotations – define meta data for database objects

It sounds useful. Something beyond database comments I presume. What this feature exactly entails is not yet clear to me. Annotations are supported on tables, views, columns, indexes, PL/SQL program units, materialized views, domains

8. Developer Role – quickly provide a user with useful privileges for doing database development

Instead of grant connect, resource to <developer user>

9. JavaScript Stored Procedures – based on the MLE (multi language engine powered by GraalVM) we can now define Stored Procedures written in JavaScript. I am curious of this means we can define larger JavaScript modules that can import each other and if we can use pre-installed JavaScript/Node modules or even import npm modules.

10. SQL Domains – the concept of a domain is very well known to anyone who ever used Oracle Designer for ERD and/or database design. A domain is generic definition of a specific type of data element – such as a name, a postal code, a country code. This generic definition can have several attributes – such as a format (check constraint), a default value, the default display format and the expression to be used when ordering by the domain based value.

In Oracle Database 23c, domains can be defined as database objects – as shown below – and when a column is defined, it can be linked to a domain definition. This is a potentially useful piece of meta data. But it is more than that: the check constraint defined against the domain is inherited by the column. The display format and order by expression can be used in query-statements through the new functions DOMAIN_DISPLAY and DOMAIN_ORDER.

11. Update records based on a join condition – for example update all employees that work in a department with a specific name. This syntax allows for a more elegant way of writing what otherwise would have to be done using an inline view or a more cumbersome where exists.

Could that FROM and WHERE clause extend to multiple tables? And why is not the JOIN clause used here? Is OUTER JOIN supported?

12, Schema Level Privileges – the ability to grant access on all objects in an schema in a single statement

For example GRANT SELECT ANY TABLE ON SCHEMA <SCOTT> TO <SOME USER>

13. JSON Schema – various options to validate documents against a JSON Schema definition. The JSON Schema can be defined on a table column – almost as a check constraint. It can be used in a query to only select values that satisfy the schema and using package dbms_json_schema validation reports can be retrieved on specific values against a JSON Schema.

14. Apparently – and I have not a clue who really needs this – in 23c we can define 4096 columns in a table. If you need that, you may have to rethink your table design.

And Finally:  BOOLEAN Data Type for column definitions and use in SQL statement. We can define a column of data type BOOLEAN and test on boolean values in the WHERE clause (with just WHERE <boolean column> – and not WHERE <boolean column> = TRUE). I am curious to see how other SQL functions will deal with boolean values.

An overview of some relevant themes in Database 23c:

some features from this list that I have not yet seen discussed in detail:

• Oracle Database <=> Kafka integration
• OAuth 2.0 integration
• PL/SQL <=> SQL transpiler (I am quite curious about that one)

..