Oracle 23c: New features

BOOLEAN data type in SQL

create table tq84_bool_test (val varchar2(10), flg boolean);
insert into tq84_bool_test values ('true'  , true );
insert into tq84_bool_test values ('false' , false);
insert into tq84_bool_test values ('null'  , null );
insert into tq84_bool_test values ('t'     ,'t'   );
insert into tq84_bool_test values ('yes'   ,'yes' );
insert into tq84_bool_test values ('true'  ,'true');
insert into tq84_bool_test values ('f'     ,'f'   );
insert into tq84_bool_test values ('0'     , 0    );

select val from tq84_bool_test where     flg;
select val from tq84_bool_test where not flg;

since

flag char(1) check (flag in ( 'Y', 'N' ))

serves the same purpose, requires the same amount of space and does the same thing.

SQL> select true, false;
--
-- TRUE FALSE
-- ---- -----
--    1     0

Selecting an expression without FROM DUAL

select
  'Hello world'     as txt,
   7 * 6            as num,
   sysdate          as now;

JavaScript Stored procedures

create mle module tq84_js language javascript as

    export function f(p1, p2) { return p1+p2; }
   
    …

  create function f (p1 number, p2 number) return number as mle module tq84_js signature f(number, number);

  select f(20, 22) /* Ha: no more dual :-) */;

DROP/CREATE TABLE IF [NOT] EXISTS

drop   table IF     EXIST tq84_t1;
create table IF NOT EXIST tq84_t2 (num number, txt varchar2(10));

GROUP BY on expression aliases/position number

select
   count(*),
   fnc(a, b) xyz
from
   t
group by
   xyz
having
   xyz > 100
;

alter session set group_by_position_enabled = true;

select
   category_id,
   sum(val)
from
   t
group by
   1;

Updating tables

udpate    dest d
   set    d.xxx = …
  from    src  s
 where    d.abc = s.def;

JSON

create table tq84_json_schema_test (
   obj json validate '
     {
         "num": number,
         … 
     }
   '
);

create or replace JSON DUALITY VIEW
from tq84_root_tab                   -- The name table sitting at the top of the hierarchy
as
   tq84_rel_dual_test
   @update @insert @delete
{
   num      :   col_num,             -- Mapping between JSON property names and table column names
   txt      :   col_txt
   children : tq84_child_tab {       -- Nesting objects
     …

   }
}

$dbHost=…
$schemaName=rene
curl -X GET http://$dbHost/ords/$schemaName/metadata-catalog/tq84_rel_dual_test

Table value constructors

insert into t1 values
  (1, 'first record' ),
  (2, 'second record'),
  (3, 'third record' ),
  (4, 'fourth record');

select * from (
  values
    (1, 'first record' ),
    (2, 'second record'),
    (3, 'third record' ),
    (4, 'fourth record')
)
tmp (num, txt);

Schema Level Privileges

grant
   select any table
on
   schema sales  
to
   rene;

SQL domains

create domain email_addr as varchar2(99);

create domain month_year as varchar2(7)
   constraint month_fmt check (regexp_like(month_year, '^\d\d-\d\d\d\d$'))
   display 'Year: ' || substr(month_year, 4, 4) || ', Month: ' || substr(month_year, 1, 2)
   order substr(month_year, 4, 4) || substr(month_year, 1, 2)
;

create table financial_report (
   id             integer    primary key,
   corrections_to email_addr,
   rep_period     month_year,
   pdf            blob
);

insert into financial_report values
( 1, 'fred@xyz.ab', '03-2023', null ),
( 2, 'fred@xyz.ab', '08-2023', null ),
( 3, 'maya@xyz.ab', '02-2023', null ),
( 4, 'joey@xyz.ab', '06-2023', null );

select
   rep_period,
   corrections_to,
   id
from
   financial_report
order by
   rep_period;
--
-- REP_PER CORRECTIONS_TO         ID
-- ------- -------------- ----------
-- 02-2023 maya@xyz.ab             3
-- 03-2023 fred@xyz.ab             1
-- 06-2023 joey@xyz.ab             4
-- 08-2023 fred@xyz.ab             2

select
   domain_display(rep_period) repper,
   corrections_to
from
   financial_report
order by
   rep_period;

-- REPPER                CORRECTIONS_TO                                                                                     
-- --------------------- ---------------------------------------------------------------------------------------------------
-- Year: 2023, Month: 02 maya@xyz.ab                                                                                        
-- Year: 2023, Month: 03 fred@xyz.ab                                                                                        
-- Year: 2023, Month: 06 joey@xyz.ab                                                                                        
-- Year: 2023, Month: 08 fred@xyz.ab                                                                                        

Developer role

begin
   dbms_developer_admin.grant_privs('rene');
end;
/

SQL> grant db_developer_role to rene identified by renesSecretPassword;
grant succeeded.

SQL> connect rene/renesSecretPassword@ora23c;
connected.

SQL> select * from session_privs order by privilege;
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.

Up to 4096 columns per table

Object and schema annotations

create table t_foo ( … )
annotations (
   expected_release 'R4.2',
   test_coverage    'no'
);

select * from user_annotations;
select * from user_annotation_values;
select * from user_annotations_usage;

«Asynchronous» transactions (lock-free reservations)

create table tq84_async (
    id    integer     primary key,
    val   number(5,2) RESERVABLE
);

insert into tq84_async values
(  13,   18.22 ),
(  19,    7.83 ),
(  42,  100.00 ),
(  99,   68.54 );

commit;

SES 1> update tq84_async set val = val + 10 where id = 42;
SES 1> select * from tq84_async where id = 42;
--
--         ID        VAL
-- ---------- ----------
--         42        100

SES 2> update tq84_async set val = val +  1 where id = 42;
SES 2> select * from tq84_async where id = 42;
--
--         ID        VAL
-- ---------- ----------
--         42        100

SES 1> commit;
SES 1> select * from tq84_async where id = 42;
--
--         ID        VAL
-- ---------- ----------
--         42        110

SES 2> select * from tq84_async where id = 42;
--
--         ID        VAL
-- ---------- ----------
--         42        110

SES 2> commit;
SES 2> select * from tq84_async where id = 42;
--
--         ID        VAL
-- ---------- ----------
--         42        111

SES 1> commit;
SES 1> select * from tq84_tab;
       ID        VAL
---------  ---------
       42        111

alter table tq84_async modify val not reservable;
drop  table tq84_async;

Improved error messages

ORA21> select sum(val), gr_1, gr_2 from T group by gr_1;
ORA-00979: not a GROUP BY expression

ORA21> connect …@ORA23

ORA23> select sum(val), gr_1, gr_2 from T group by gr_1;
ORA-00979: "GR_2": must appear in the GROUP BY clause or
                   be used in an aggregate function

Misc

Docker image

docker pull container-registry.oracle.com/database/free

$ docker rm ora23c

docker run  --name ora23c -d -p 1521:1521 container-registry.oracle.com/database/free

docker ps

$ docker exec ora23c sh -c "echo 'alter user sys identified by IamTheDBA;' | sqlplus -S / as sysdba"

User altered.

$ docker exec -it ora23c bash
bash-4.4$ sqlplus / as sysdba
…
SQL>

Links