Oracle 26ai

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Version 23.26.3.0.0
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
0


begin
  execute immediate 'create table customer (id number primary key, name varchar2(10) not null)';
  execute immediate 'create table product (id number primary key, name varchar2(10) not null, price number(6,2) not null)';
  execute immediate 'create table orders (id number primary key, customer_id number not null references customer, ordered_on date not null)';
  execute immediate 'create table order_line (order_id number references orders, product_id number references product, qty number not null, primary key (order_id, product_id))';
  execute immediate q'[insert into customer values (1,'ann'), (2,'bob'), (3,'cat')]';
  execute immediate q'[insert into product values (1,'widget',2.50), (2,'gadget',7.00), (3,'gizmo',12.00)]';
  execute immediate q'[insert into orders select level-1, 1+mod(level-1,3), date '2026-01-01' + (level-1)*7 from dual connect by level <= 52]';
  execute immediate 'insert into order_line select o.id, p.id, 1+mod(o.id*p.id,5) from orders o cross join product p where mod(o.id+p.id,2)=0 or p.id=1';
end;
/

1 rows affected


-- new in 26ai: assertions
create assertion gizmos_by_the_handful check (not exists (select * from order_line l where l.qty > 5 and exists (select * from product p where p.id = l.product_id and p.name = 'gizmo')));
insert into order_line values (2, 3, 9);

ORA-08601: Assertion (FIDDLE.GIZMOS_BY_THE_HANDFUL) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


-- sqlplus demo
set pagesize 50 feedback off
column customer format a8
column product format a8
column revenue format 9,990.00
break on customer skip 1
compute sum of revenue on customer
select c.name customer, p.name product, sum(l.qty * p.price) revenue
from customer c join orders o on o.customer_id = c.id join order_line l on l.order_id = o.id join product p on p.id = l.product_id
group by c.name, p.name
order by c.name, p.name;

CUSTOMER PRODUCT    REVENUE
-------- -------- ---------
ann	 gadget      182.00
	 gizmo	     336.00
	 widget      130.00
********	  ---------
sum		     648.00

bob	 gadget      161.00
	 gizmo	     348.00
	 widget      130.00
********	  ---------
sum		     639.00

cat	 gadget      189.00
	 gizmo	     264.00
	 widget      122.50
********	  ---------
sum		     575.50


-- echarts demo
with
  mon (m) as (select add_months(date '2026-01-01', level-1) from dual connect by level <= 12)
, rev as
    ( select p.name, mon.m, coalesce(sum(l.qty*p.price),0) r
      from
        product p cross join
        mon left join
        (orders o join order_line l on l.order_id = o.id) on trunc(o.ordered_on, 'MM') = mon.m and l.product_id = p.id
      group by p.name, mon.m )
select
  json_object
  ( 'tooltip': json_object('trigger': 'axis')
  , 'legend': json_object()
  , 'xAxis': json_object('type': 'category', 'data': (select json_arrayagg(to_char(m, 'Mon') order by m) from mon) format json)
  , 'yAxis': json_object('type': 'value')
  , 'series': (select json_arrayagg(json_object('name': name, 'type': 'bar', 'stack': 'revenue', 'data': vals format json) order by name) from (select name, json_arrayagg(r order by m) vals from rev group by name)) format json
  ) as "option"
from dual;


option


{"tooltip":{"trigger":"axis"},"legend":{},"xAxis":{"type":"category","data":["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]},"yAxis":{"type":"value"},"series":[{"name":"gadget","type":"bar","stack":"revenue","data":[70,35,42,49,21,63,42,63,35,42,49,21]},{"name":"gizmo","type":"bar","stack":"revenue","data":[108,36,84,96,84,72,60,108,36,144,36,84]},{"name":"widget","type":"bar","stack":"revenue","data":[37.5,25,27.5,37.5,30,32.5,37.5,35,25,37.5,27.5,30]}]}


-- mermaid demo
with
  k as
    ( select /*+ materialize */ cc.table_name, cc.column_name, uc.constraint_type, uc.constraint_name, uc.r_constraint_name
      from user_constraints uc join user_cons_columns cc on cc.constraint_name = uc.constraint_name
      where uc.constraint_type in ('P', 'R') )
, cols as
    ( select c.table_name, listagg(rtrim('    ' || lower(c.data_type) || ' ' || lower(c.column_name) || ' ' || (select listagg(decode(constraint_type, 'P', 'PK', 'FK'), ', ') within group (order by constraint_type) from k where k.table_name = c.table_name and k.column_name = c.column_name)), chr(10)) within group (order by c.column_id) cols
      from user_tab_columns c
      where c.table_name not like 'ORA$%'
      group by c.table_name )
select
  'erDiagram' || chr(10) ||
  (select listagg('  ' || lower(table_name) || ' {' || chr(10) || cols || chr(10) || '  }', chr(10)) within group (order by table_name) from cols) || chr(10) ||
  (select listagg('  ' || lower(fk.table_name) || ' }o--|| ' || lower(pk.table_name) || ' : "' || lower(fk.column_name) || '"', chr(10)) within group (order by fk.table_name, pk.table_name) from k fk join k pk on pk.constraint_name = fk.r_constraint_name where fk.constraint_type = 'R') as diagram
from dual;


DIAGRAM


erDiagram
  customer {
    number id PK
    varchar2 name
  }
  orders {
    number id PK
    number customer_id FK
    date ordered_on
  }
  order_line {
    number order_id PK, FK
    number product_id PK, FK
    number qty
  }
  product {
    number id PK
    varchar2 name
    number price
  }
  orders }o--|| customer : "customer_id"
  order_line }o--|| orders : "order_id"
  order_line }o--|| product : "product_id"