Oracle Database 23 FREE – Developer release is available! In the blog posting Announcing Oracle Database 23ai : General Availability you can find important information on it. In addition to this, there is a documentation page, a user forum page, the product website and more. All interesting links are summarized at the end of this posting or can be found on the GitHub page Oracle Database 23ai.
What is this release all about? Responding to the demand from developers for access to the newest Oracle database developer features, Oracle is, for the first time ever, releasing the next version of the Oracle Database to the developer community first, starting with Oracle Database 23c Free – Developer Release (short Oracle Database 23c Free). In May 2024, Oracle announced the next release named Oracle Database 23ai which is also available as Oracle Database Free release.
How can you use it? If you are interested in downloading the software, trying the Docker repo or downloading the VirtualBox VM image, you can visit the following pages:
The following development software is usually not included in Oracle Database 23 Free – except for the VirtualBox Appliance. You can download them separately and use them with Oracle Database 23 Free:
- Oracle APEX
- Oracle Developer Tools for Visual Studio
- Oracle REST Data Services (ORDS)
- Oracle SQL Developer
However in the case of Oracle Database 23ai Free – Developer Release VirtualBox Appliance you will get a virtual guest appliance that provides pre-configured Oracle software such as Oracle Linux 8.8, Oracle Database 23ai Free, Oracle REST Data Services 24.1.0, Oracle SQLcl 24.1.0 and Oracle APEX 23.2.
The Installation Guide is also available where you can find notes on installation and on limitations for this release. In short be aware of the following:
- The maximum amount of user data in Oracle Database Free cannot exceed 12 GB.
- The maximum amount of RAM for Oracle Database Free cannot exceed 2 GB.
- Oracle Database Free limits itself automatically to two cores for processing.
- Oracle Database Free restricts itself to only one installation per logical environment.
If you want to get an overall overview and a short description of all the new features provided by Oracle Database Free, check out the New Features guide. In the Database Licensing Information User Manual you will find a list of permitted features and options in chapter 1.3 Permitted Features, Options, and Management Packs by Oracle Database Offering.
My colleagues Witold, Stephane and I have started to investigate the Oracle Database 23 features and tested some SQL features first after a quick and easy installation of Oracle Database Free using RPM packages.
In this posting you find a short presentation of the following 10 SQL features. More features will be presented in upcoming postings.
- Aliases in GROUP BY clause
- FROM clause – now optional
- Boolean for SQL
- IF [NOT] EXISTS DDL clause
- New table value constructor
- Multivalue INSERTs
- RETURNING clause of UPDATE and MERGE statement
- Joins in UPDATE and DELETE
- Annotations. new metadata for database objects
- “Light weight object types” with SQL Domains
Please note the order of the feature list doesn’t reflect any ranking of importance.
Aliases in GROUP BY clause
Oracle Database 23c, among of a lot of other features, introduces the ability to use aliases in GROUP BY clause of a SELECT statement. This feature simplifies writing queries with complex expressions as well as ensures better compatibility with some other relational databases, like Teradata, MySQL and PostgreSQL so for example:
SELECT to_char(hiredate,'YYYY') "Year", count(*) FROM emp GROUP BY to_char(hiredate,'YYYY');
In Oracle Database 23 this can be written in a bit simpler way:
SELECT to_char(hiredate,'YYYY') "Year", count(*) FROM emp GROUP BY "Year"; Year COUNT(*) ---- ---------- 1981 10 1987 2 1980 1 1982 1
FROM clause – now optional
Another interesting feature introduced in Oracle Database 23c is optionality of FROM clause in SELECT statements. Up to this version this clause was obligatory. However there are some databases in the market, like MS SQL Server, MySQL and PostgreSQL, where this clause is optional. Since then, as in previous case, this feature ensures better compatibility with such databases. So, for example, the following statement executed in any earlier, than 23c, version of Oracle Database:
SQL> SELECT sysdate;
returns the following error:
select sysdate * ERROR at line 1: ORA-00923: FROM keyword not found where expected
While it is executed succesfully in Oracle Database 23:
SQL> SELECT sysdate; SYSDATE --------- 04-APR-23
Boolean for SQL
Oracle Database 23c introduces the new BOOLEAN datatype. This leverages the use of true boolean columns/variables, instead of simulating them whith a numeric value. The ability to write boolean predicates simplifies the syntax of SQL statements.
create table TEST_BOOLEAN ( name VARCHAR2(100), IS_SLEEPING BOOLEAN); Table created.
SQL> alter table TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL); Table altered.
SQL> alter table TEST_BOOLEAN modify (IS_SLEEPING default FALSE); Table altered.
SQL> insert into TEST_BOOLEAN (name) values ('Mick');
1 row created.
SQL> insert into TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');
1 row created.
SQL> insert into TEST_BOOLEAN (name, is_sleeping) values ('Ron',1);
1 row created.
SQL> select name from test_boolean where not is_sleeping; NAME -------------------------------------------------------------------------------- Mick Keith SQL> set linesize window SQL> select * from test_boolean; NAME IS_SLEEPING ---------------------------------------------------------------------------------------------------- ----------- Mick FALSE Keith FALSE Ron TRUE
SQL> select dump(is_sleeping) from test_boolean where name = 'Ron';
DUMP(IS_SLEEPING) -------------------------------------------------------------------------------- Typ=252 Len=1: 1
IF [NOT] EXISTS DDL clause
Starting with Oracle Database 23c, the new “IF [NOT] EXISTS” DDL clause allows to decide how DDL errors will be handled. This simplifies the DDL scripting, as potential errors due to objects existence or inexistence can be hidden to the scripting.
Let’s assume the table DEPT exists:
SQL> desc dept Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
SQL> create table if not exists scott.dept (deptno number, dname varchar2(10), loc varchar2(15)); Table created.
Now let’s drop the helper table dept1 …
SQL> create table dept1 as select * from dept; Table created. SQL> drop table if exists dept1; Table dropped.
New table value constructor
Starting from Oracle database 23c, table values constructor has been extended. It can now be used in INSERT statements, to create several rows in a single command.It can also be used in SELECT statements, and in the view factorization syntax. In this last case, it simplifies the syntax of the statements, and avoid using the DUAL table.
The following statement looks like a kind of table function on the fly …
SQL> select * from (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname); DEPTNO DNA ---------- --- 50 HR 60 DEV 70 AI
More information on this can be found in New value constructor in 23.
Multivalue INSERTs
Another interesting feature ensuring better coexistence and compatitility with other frequently used database management systems is multivalue INSERT statement.
In previous versions of Oracle database, to insert, for example 3 rows it was needed to execute three separate insert statements, for example:
insert into DEPT values (50,'HR','LOS ANGELES'); insert into DEPT values (60,'IT','SAN FRANCISCO'); insert into DEPT values (70,'MANUFACTURING','DETROIT');
Oracle Database 23, smilarly to other databases, like PostgreSQL, introduced the new syntax allowing for inserting all these rows in a single one INSERT statement, so you may insert several tuples in one DML…
SQL> insert into DEPT values (50,'HR','LOS ANGELES'), (60,'IT','SAN FRANCISCO'), (70,'MANUFACTURING','DETROIT') / 3 rows created.
Among better compatibility with some other databases, this statement can be also used to ensure consistency of some insert operations in autocommit mode, which can be important e.g. for some APEX applications using this mode to work on a data.
RETURNING clause of UPDATE and MERGE statement
This clause has been implemented long time ago as a part of EXECUTE IMMEDIATE statement. However in Oracle Database 23c we can find it as a part of traditional, static DML statements – in this case it allows for obtaining old and new values of columns from a row processed by such statement:
SQL> SELECT ename, sal FROM emp WHERE ename = 'KING'; ENAME SAL ---------- ---------- KING 5000 SQL> VARIABLE old_salary NUMBER SQL> VARIABLE new_salary NUMBER SQL> UPDATE emp SET sal=sal+1000 WHERE ename = 'KING' 2 RETURNING OLD sal, NEW sal into :old_salary, :new_salary 3 / 1 row updated. SQL> PRINT old_salary OLD_SALARY ---------- 5000 SQL> PRINT new_salary NEW_SALARY ---------- 6000
Joins in UPDATE and DELETE
You may update table date via joins – based on foreign table conditions. There is no need for sub selects or IN clause. For example in instead of using prior to 23c the folliwng statements
update emp e set e.sal=e.sal*2 where e.deptno in (select d.deptno from dept d where e.deptno=d.deptno and d.dname='RESEARCH')
You may use now …
SQL> update emp e set e.sal=e.sal*2 from dept d where e.deptno=d.deptno and d.dname='RESEARCH'; 5 rows updated.
Annotations, new metadata for database objects
Annotations are optional meta data for database objects. An annotation is either a name-value pair or name by itself. The name and optional value are freeform text fields. An annotation is represented as a subordinate element to the database object to which the annotation has been added. Supported schema objects include tables, views, materialized views, and indexes. With annotations you may store and retrieve metadata about a database objects. You can use it to customize business logic, user interfaces or provide metada to metatdata repositories. It can be added with CREATE or ALTER statement. – on table or column level.
With annotations you may store and retrieve metadata about a database objects. You can use it to customize business logic, user interfaces or provide metada to metatdata repositories.
Let’s create an annotated table EMP_ANNOTATED with column and table annotations.
create table emp_annotated (empno number annotations(identity, display 'person_identity', details 'person_info') , ename varchar2(50) , salary number annotations(display 'person_salary', col_hidden) ) annotations (display 'employee_table') /
Data Dictionary views such as USER_ANNOTATIONS and USER_ANNOTATIONS_USAGE can help to monitor the usage. More information can be found in the post Schema Annotations – the new metadata.
“Light weight object types” with Domains
A domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints and is created with a CREATE DOMAIN statement. Domains provide constraints, display, ordering and annotations attributes. After you define a domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain’s optional properties and constraints to those columns.
Domains allow users to declare the intended usage for columns. They are data dictionary objects so that abstract domain specific knowledge can be easily reused.
SQL> create domain yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');
SQL> create table person
( id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');
Table created.
SQL> desc person
Name Null? Type
-------------------------------------------------------------------------- -------- ----------------------------
ID NUMBER(5)
NAME VARCHAR2(50)
SALARY NUMBER
PERSON_BIRTH NUMBER(4) SCOTT.YEARBIRTH
SQL> insert into person values (1,'MARTIN',3000, 1988);
With the new function DOMAIN_DISPLAY you can display the property.
SQL> select DOMAIN_DISPLAY(person_birth) from person; DOMAIN_DISPLAY(PERSON_BIRTH) ------------------------------------------- 19-88
Domain usage and Annotations can be monitored with data dictionary views, e.g.
SQL> col object_name format a25 SQL> col column_name format a15 SQL> col domain_name format a20 SQL> col domain_owner format a10 SQL> col annotation_name format a15 SQL> col annotation_value format a20 SQL> select * from user_annotations_usage; OBJECT_NAME OBJECT_TYP COLUMN_NAME DOMAIN_NAM DOMAIN_OWN ANNOTATION_NAME ANNOTATION_VALUE --------------- ---------- --------------- ---------- ---------- -------------------- ---------------- EMP_ANNOTATED TABLE DISPLAY employee_table PERSON TABLE DISPLAY person_table EMP_ANNOTATED TABLE EMPNO IDENTITY EMP_ANNOTATED TABLE EMPNO DISPLAY person_identity EMP_ANNOTATED TABLE EMPNO DETAILS person_info EMP_ANNOTATED TABLE SALARY DISPLAY person_salary EMP_ANNOTATED TABLE SALARY COL_HIDDEN YEARBIRTH DOMAIN TITLE yearformat PERSON TABLE PERSON_BIRTH YEARBIRTH SCOTT TITLE yearformat
More information on this can be found in Less coding using Domains.