Oracle 23c: Novos recursos

Recentemente, a Oracle liberou a versão gratuita do Oracle Database 23c conhecida como Oracle Database 23c Free — Developer Release que permite aos desenvolvedores terem acesso antecipado há novos recursos e funcionalidades do banco de dados mais avançadas do mundo. A versão do desenvolvedor precede o lançamento planejado do Oracle Database 23c, o próximo lançamento de suporte de longo prazo (long term release).

Inúmeros novos recursos e aprimoramentos estão disponíveis em comparação à versão anterior. Abaixo destaco alguns dos recursos que, particularmente, acho mais relevantes.

Consulta de expressão sem a cláusula FROM

A tabela DUAL não é mais necessária para selecionar uma ou mais expressões.

SQL> select sysdate;

SYSDATE
---------
01-MAY-23

SQL> select sysdate from dual;

SYSDATE
---------
01-MAY-23

Suporte a sintaxe IF [NOT] EXISTS

A criação, modificação e exclusão de objetos agora suporta os modificadores de sintaxe IF EXISTS e IF NOT EXISTS. Isso permite que seja controlado se um erro deve ser apresentado quando determinado objeto existir ou não.

SQL> drop table t1_exemplo;
drop table t1_exemplo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table IF EXISTS t1_exemplo;

Table dropped.

SQL> create table t1_exemplo (x number, y varchar2(100));

Table created.

SQL> create table t1_exemplo (x number, y varchar2(100));
create table t1_exemplo (x number, y varchar2(100))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create table IF NOT EXISTS t1_exemplo (x number, y varchar2(100));

Table created.

Agrupamento de coluna por alias ou posição

Agora é possível usar alias de coluna ou posição de item nas consultas com cláusulas GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP e GROUP BY GROUPING SETS, além da cláusula HAVING oferecer suporte a alias de coluna.

SQL> SELECT y,
  2         count(*) qtd
  3  FROM t1_exemplo
  4  GROUP BY y
  5  HAVING qtd > 1;

Y                 QTD
---------- ----------
AAA                 2

Anteriormente a consulta retornaria a mensagem de erro ORA-00904: invalid identifier.

Se o parâmetro group_by_position_enabled for definido como true, também será possível agrupar por um número de posição em vez de um alias de expressão:

SQL> alter session set group_by_position_enabled = true;

Session altered.

SQL> SELECT y,
  2         count(*) qtd
  3  FROM t1_exemplo
  4  GROUP BY 1;

Y                 QTD
---------- ----------
AAA                 2
BBB                 1
CCC                 1

Construtores de valor de tabela

Permite a inserção de vários registros de uma única vez (table value constructor):

SQL> INSERT INTO t1_exemplo
  2  VALUES (1, 'AAA'),
  3         (2, 'AAA'),
  4         (3, 'BBB'),
  5         (4, 'CCC');

4 rows created.

Similar para instruções SELECT:

SQL> SELECT *
  2  FROM (
  3        VALUES (1, 'registro 1'),
  4               (2, 'registro 2'),
  5               (3, 'registro 3'),
  6               (4, 'registro 4'))
  7   tmp (x, y);

         X Y
---------- ----------
         1 registro 1
         2 registro 2
         3 registro 3
         4 registro 4

Permissões a nível de schema

Os privilégios de schema simplificam a concessão onde um usuário ou função precisa de privilégios em todos os objetos de um schema.

-- Sequences

grant select any sequence on schema user1 to user2;
grant select any sequence on schema user1 to user1_role;

-- Tables, views, materialized views
grant select any table on schema user1 to user2;
grant insert any table on schema user1 to user2;
grant update any table on schema user1 to user2;
grant delete any table on schema user1 to user2;
grant select any table on schema user1 to user1_role;
grant insert any table on schema user1 to user1_role;
grant update any table on schema user1 to user1_role;
grant delete any table on schema user1 to user1_role;

-- Procedures, functions and packages
grant execute any procedure on schema user1 to user2;
grant execute any procedure on schema user1 to user1_role;

Role de banco de dados para desenvolvedores

A role DB_DEVELOPER_ROLE fornece ao desenvolvedor todos os privilégios necessários para projetar, implementar, depurar e implantar aplicativos em bancos de dados Oracle.

SQL> grant db_developer_role to user1;

Grant succeeded.

SQL> conn user1/user1@freepdb1
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

PRIVILEGE
----------------------------------------
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE

PRIVILEGE
----------------------------------------
FORCE TRANSACTION
ON COMMIT REFRESH

24 rows selected.

Número de colunas por tabela e visão aumentados para 4096

MAX_COLUMNS, novo parâmetro de inicialização introduzido na versão 23c. Se ajustado para EXTENDED permite que as tabelas e visões do banco de dados tenham como limite 4096 colunas em vez de 1000. Além do ajuste no parâmetro MAX_COLUMNS é preciso que o parâmetro compatible esteja configurado como 23.0.0

SQL> alter session set container=FREEPDB1;

SQL> alter system set max_columns=EXTENDED scope=spfile;

System altered.

SQL> shutdown immediate;
Pluggable Database closed.

SQL> startup;
Pluggable Database opened.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      23.0.0

SQL> show parameter max_columns

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      EXTENDED

declare
  l_col_count number := 4096;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1_exemplo purge';
  
  l_str := 'create table t1_exemplo (';
  for i in 1 .. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';
  
  execute immediate l_str;
end;
/

PL/SQL procedure successfully completed.


SQL> select count(*) from dba_tab_columns where table_name='T1_EXEMPLO';

  COUNT(*)
----------
      4096