Oracle Database 23c Free - Feature Highlights

In an effort to give developers a head start with the new features coming out in the database 23c release, Oracle recently released Oracle Database 23c Free.  A virtual image with a copy of the database is available for download from Oracle's website.  Included in in the virtual image are the following:

• Oracle Linux 8.7
• Oracle Database 23.2 Free - Developer Release for Linux x86-6
• Oracle REST Data Services 23.1
• Oracle SQLcl 23.1
• Oracle APEX 22.2
• Schema with sample data

The database is limited to 12GB of user data storage and maximum RAM is 2GB. While this sufficient space and memory to test out the new database features, it is not intended for production applications. 

In less than an hour from starting the download, I was working in the new 23c instance.  Most of  the examples are from the HR sample schema provided by Oracle.  

Starting with the important question first:  When is Oracle Database 23c going to be released?

If you ask when when the Oracle database 23c is going to be released,  you will get a different vague answer depending whether you are a cloud or on-prem customer and who you ask. I am thinking it will be released around calendar year-end or maybe early next year.  For those on in the cloud, your migration schedule will follow the release date.

This blog will cover a few features that developers will appreciate.  The goal is to provide follow-up blogs over the next several months with additional feature highlights. 

Domains

Domains are a single point of definition for columns.  This will allow the developer to define a column domain and use it through out the database application.  Below is an example that can be found in several places on the internet.  This example is from a blog on Oracle's website written by Ulrike Schwinn, Distinguished Data Management Expert.  

Creating Domain

create domain if not exists myemail_domain AS VARCHAR2(100)

default on null 'XXXX' || '@missingmail.com'

constraint email_c CHECK (regexp_like (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$'))

display substr(myemail_domain, instr(myemail_domain, '@') + 1);

create table person

     ( p_id number(5),

       p_name varchar2(50),

       p_sal number,

       p_email varchar2(100) domain myemail_domain

      );

This will help with column definition consistency throughout your database application.  To learn more about domains, I recommend reading Ulrike's detailed blog.  

GROUP BY and HAVING Clause with Column Aliases

Starting with the 23c release, the GROUP BY and HAVING clause will be able to use column aliases. No more need to to re-enter long column calculations.  Below is an example using the sample schema data that Oracle provided.

Outlined in red  are the GROUP BY and HAVING clauses using the aliases. In the example above, the column formulas aren't too complicated but what if we had a nested case statement or complex calculation?  

GROUP BY with Column Position

Just like in the ORDER BY clause,  we will be able to use column positions in the GROUP BY clause.   Below is an example of a simply GROUP BY using the column position.

Notice the ALTER SESSION statement just above the SELECT statement.  The group_by_position_enabled variable is set to 'false' by default.  It must be set to 'true' at the system level or for the current session to take advantage of the GROUP BY column position feature.  

SELECT without FROM Clause

You can now run SELECT expression-only queries without a FROM clause.  No more FROM dual is needed.  If  a “;” is placed at the end, a red dot and squiggly line appear, indicating incorrect code, but the statement runs fine.

Direct Joins for UPDATE and DELETE Statements

Starting with Oracle database 23c onward,  we are allowed to use direct joins to tables to drive UPDATE and DELETE statements. Below is an example using the HR schema provided by Oracle. A new column, department_name, was created in the EMPLOYEES table.  This column was added to easily demonstrate this new feature. 

EMPLOYEES table after adding department_name column:

DEPARTMENTS table

EMPLOYEES table after running the simple UPDATE command below:

This feature will simplify coding for UPDATES and DELETES.

Oracle has 300 plus new features, some that worth spending some tine to explore are:

• New Graph SQL standard (Don't have to know PGQL)
• JSON support
• SQL Transpiler  (allows functions to be converted to SQL for better performance)
• Temporary Access/Privileges
• New Developer Role
• Efficient Table DDL Change Notification
• Better Return Clauses (provides old and new values when applicable)
• If [NOT] EXISTS Syntax Support
• DEFAULT ON NULL for UPDATE and INSERT Statements
• Aggregation over INTERVAL datatypes
• Boolean Data Types

I hope you find these features as helpful as I have.  Enjoy and happy coding.