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

Lucas Jellema 1
1 0
Read Time:5 Minute, 19 Second

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.

image

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!

image

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.

image

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)

imageimage


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.

image

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.image

image

image

image

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

image

image

image

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

Instead of grant connect, resource to <developer user>

image

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.

image

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.

image

image

image

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?

image

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>

image

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.

image

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.

image

An overview of some relevant themes in Database 23c:image

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)



..

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Oracle Database 23c–new feature highlights (release February 2023?)

  1. Just a quick footnote on the motivation for large column numbers.
    In typical relational design, then yes … time to have a think about that model 🙂

    But a common requirement in machine learning models is to take a set of data (rows) and pivot that into a column for each distinct row value, before applying the various algorithms. That’s the target audience here – when you pivot that data for machine learning, you might need 1000s of columns

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Post

Adding Podman to my VM with Minikube (part 1)

For a demo, I needed an environment with Minikube, using the Podman driver as an alternative container runtime to the Docker driver. Lucky for me, I had the configuration for such an environment using Vagrant and Oracle VirtualBox. In the past, I already set up such a demo environment, available […]
%d bloggers like this: