dbi Blog

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 :

user_annotations
user_annotation_values
user_annotations_usage
user_annotation_text_metadata

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:

Oracle 23c: New features (renenyffenegger.ch)

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:

SQL >

variable v_role VARCHAR2(30)
exec :v_role := 'DB_DEVELOPER_ROLE';

-- System Privileges
select sp.privilege
from   dba_sys_privs sp
where  sp.grantee = :v_role
order by 1;

PRIVILEGE
----------------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DIMENSION
CREATE DOMAIN
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE MINING MODEL
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE
FORCE TRANSACTION
ON COMMIT REFRESH

24 rows selected.

SQL>

-- Role Privileges
select rp.granted_role
from   dba_role_privs rp
where  rp.grantee = :v_role
order by 1;

GRANTED_ROLE
--------------------------------------------------------------------------------
CTXAPP
SODA_APP

SQL>

-- Object Privileges
column privilege format a30
column table_name format a30

select tp.privilege, tp.table_name 
from   dba_tab_privs tp
where  tp.grantee = :v_role
order by 1, 2;

PRIVILEGE                      TABLE_NAME
------------------------------ ------------------------------
EXECUTE                        JAVASCRIPT
READ                           V_$PARAMETER
READ                           V_$STATNAME
SELECT                         DBA_PENDING_TRANSACTIONS

cf. ORACLE-BASE – DB_DEVELOPER_ROLE Role in Oracle Database 23c

Normally the privileges will be sufficient. The only permissions missing are in comparison to:

CONNECT ROLE:

SET CONTAINER

RESOURCE ROLE:

CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROPERTY GRAPH

SQL-domains

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.