In this blog I want to introduce some of the new features of the Oracle 23c developer edition. It will be only a selection of some of the main features. It is not the intention to give you a thorough picture of all the features in this blog. I have tested some of the essential ones (in my opinion) on the new edition and I want to indicate some restrictions or deficiencies I’ve figured out:
- Up to 4.096 columns per table:
By default the maximum number of columns allowed for a table is 1000. If max_columns is set to extended and compatible=23.0.0 you can build tables with up to 4.096 columns.
SQL> show parameter compatible NAME TYPE VALUE ----------------- ------- ------ compatible string 23.0.0 SQL> show parameter max_columns NAME TYPE VALUE ----------------- ------- ------ max_columns string EXTENDED
From clause is not necessary for several selects:
For some select you don’t need the from (dual) clause, you can now query values, conversions or calculations in that easy way:
- “New” datatype BOOLEAN:
Now we can define in Oracle 23c the datatype Boolean, using the keywords BOOL or BOOLEAN. For that I’ve built a small table with boolean flags:
SQL> create table bool_t1 (name varchar2(20), flag boolean); insert into bool_t1 values ('no plan', false); insert into bool_t1 values ('plan', yes); insert into bool_t1 values ('no plan', 0); insert into bool_t1 values ('plan', 1); insert into bool_t1 values ('plan', 'yes'); insert into bool_t1 values ('no plan', 'no'); insert into bool_t1 values ('plan', true); insert into bool_t1 values ('dont know', 't'); insert into bool_t1 values ('dont know', 'f'); commit;
Any select will give you now the boolean flag (0 or 1):
- Drop/create table if (not) exists
For create or drop table we can now mention the condition “if exists” or ”if not exists”, which sounds clear for creating a table if it not exists or dropping a table which exists. It works fine anyway, but there are some teething troubles with that feature regarding the sqlplus-messages:
- In my examples the table “IF” was mentioned in the confirmation but the right table “NAME_LIST” was treated!
- The message was, that the table “NAME_LIST” has been dropped, but it has existed not at all!
- Object and schema annotations
With Oracle 23c you can now provide comments (annotations) for object-data and schemas. You see an example for a simple table with annotations:
Annotations can checked with the following (user/all/dba) views :
- Lock-free reservations
If a column with type NUMER is now marked as RESERVABLE multiple transactions for concurrent updates are allowed on it, so called “asynchronous” transactions.
RESERVABLE columns are possible for tables with primary key (naturally because of the locks) and a column that contains no NULL values!
And the column can not be dropped before setting it to NOT RESERVABLE.
There is a nice demonstration regarding the update-improvements, which you can see here:
- Conditional table updates
Another feature are combined and/or conditional updates with joins over several tables. It works quite good, with a small restriction:
Updates with condition in join-clause, but not working on tables with reservable columns!
- Inserting multiple records with “table value constructors”
So called “table values constructors” are introduced in Oracle 23c which allow us allows us to insert multiple rows into a table in one single step or operation and the same constructor can even be used for queries in the the FROM-clause
- Schema-level privileges
In former versions it was a bit tricky to grant privileges of on schema. Now you can accomplish this with one single command:
SQL> grant select any table on schema demo_user to scott;
GROUP BY and HAVING on position or alias
With Oracle 23c you can easily use column aliases or position numbers in GROUP BY and HAVING clauses without to specify the complete references defined. It works if group_by_position_enabled is set to true!
- New developer role (db_developer_role)
For developers’ purposes the new db_developer_role is available now, but not yet the dbms_developer_admin package in Oracle 23c free edition:
Which privileges are in the role? We can check it with the queries below:
Normally the privileges will be sufficient. The only permissions missing are in comparison to:
- SET CONTAINER
- CREATE CLUSTER
- CREATE INDEXTYPE
- CREATE OPERATOR
- CREATE PROPERTY GRAPH
With so called SQL-domains we can now define data properties and constraints not only on object level, but also as one single Point Of Definition (SPOD). The definition related to a column can now be reused accross the whole schema or database.
This is only a short overview over domains in Oracle 23c, there are pretty more options like multi clolumn domains and constraints.
Information about domains is stored in DBA_DOMAINS and relevant SQL-functions are
- New PL/SQL packages and further functionalities
There are three new packages in Oracle 23c
I will give an overview to this in the next blog and especially to some JSON features and blockchain table enhancements.
- Sources and complementary articles