Support New Features/Changes in SQL Grammar 23c

[PhilippSalvisberg]

The currently supported SQL grammar is 21c. See supported versions

We should add support for 23c.

Reference

See

• Oracle Database New Features 23c
• SQL Language Reference 23c
• Graph Developer's Guide for Property Graph
• JavaScript Developer's Guide
• Database Concepts
• Database Development Guide

New Features in SQL Language Reference 23c

• [x] SQL Support for Boolean Datatype
  • [x] Boolean Data Type
  • [x] BOOLEAN Expressions (e.g. can be used in select_list)
• [x] Native Representation of Graphs in Oracle Database
  • [x] ALTER PROPERTY GRAPH
  • [x] CREATE PROPERTY GRAPH
  • [x] DROP PROPERTY GRAPH
• [x] Support for the ISO/IEC SQL Property Graph Queries (SQL/PGQ) Standard
  • [x] GRAPH_TABLE Operator
• [x] Direct Joins for UPDATE and DELETE Statements
  • [x] Update With Direct-Join with from_using_clause
  • [x] Delete With Direct-Join with from_using_clause
• [x] Multilingual Engine Module Calls
  • [x] CREATE MLE ENV
  • [x] CREATE MLE MODULE
  • [x] ALTER MLE ENV
  • [x] ALTER MLE MODULE
  • [x] DROP MLE ENV
  • [x] DROP MLE MODULE
• [x] DEFAULT ON NULL for UPDATE Statements
  • [x] [CREATE TABLE](on null clause)](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__CJABFBAA)
• [x] GROUP BY Column Alias or Position
  • [x] SELECT
• [x] SELECT Without FROM Clause
  • [x] SELECT (query_block)
• [x] SQL UPDATE RETURN Clause Enhancements
  • [x] returning_clause in INSERT, UPDATE, DELETE
• [x] Usage Domains
  • [x] ALTER DOMAIN
  • [x] DROP DOMAIN
  • [x] CREATE DOMAIN
• [x] DBMS Blockchain Versions
  • [x] CREATE BLOCKCHAIN TABLE
• [x] CEIL FLOOR for DATE, TIMESTAMP, and INTERVAL Types
  • [x] CEIL
  • [x] FLOOR
  • [x] ROUND
  • [x] TRUNC
• [x] IF [NOT] EXISTS Syntax Support
  • [x] Supported Object Types
    • [x] CREATE ... IF NOT EXISTS
      • [x] CREATE ANALYTIC VIEW
      • [x] CREATE ATTRIBUTE DIMENSION
      • [x] CREATE CLUSTER
      • [x] CREATE DATABASE LINK
      • [x] CREATE DIRECTORY
      • [x] CREATE SINGLE COLUMN DOMAIN
      • [x] CREATE MULTI COLUMN DOMAIN
      • [x] CREATE FLEXIBLE DOMAIN
      • [x] CREATE EDITION
      • [x] CREATE FUNCTION
      • [x] CREATE HIERARCHY
      • [x] CREATE INDEX
      • [x] CREATE INDEXTYPE
      • [x] CREATE INMEMORY JOIN GROUP
      • [x] CREATE JAVA
      • [x] CREATE JSON RELATIONAL DUALITY VIEW
      • [x] CREATE LIBRARY
      • [x] CREATE MATERIALIZED VIEW
      • [x] CREATE MATERIALIZED VIEW LOG
      • [x] CREATE MLE ENV
      • [x] CREATE MLE MODULE
      • [x] CREATE OPERATOR
      • [x] CREATE PACKAGE
      • [x] CREATE PACKAGE BODY
      • [x] CREATE PROCEDURE
      • [x] CREATE PROPERTY GRAPH
      • [x] CREATE SEQUENCE
      • [x] CREATE SYNONYM
      • [x] CREATE TABLE
      • [x] CREATE TRIGGER
      • [x] CREATE TYPE
      • [x] CREATE TYPE BODY
      • [x] CREATE USER
      • [x] CREATE VIEW
    • [x] ALTER ... IF EXISTS
      • [x] ALTER ANALYTIC VIEW
      • [x] ALTER ATTRIBUTE DIMENSION
      • [x] ALTER CLUSTER
      • [x] ALTER DATABASE LINK
      • [x] ALTER DOMAIN
      • [x] ALTER FUNCTION
      • [x] ALTER HIERARCHY
      • [x] ALTER INDEXTYPE
      • [x] ALTER INMEMORY JOIN GROUP
      • [x] ALTER JAVA
      • [x] ALTER LIBRARY
      • [x] ALTER MATERIALIZED VIEW
      • [x] ALTER MATERIALIZED VIEW LOG
      • [x] ALTER MATERIALIZED ZONEMAP
      • [x] ALTER MLE ENV
      • [x] ALTER MLE MODULE
      • [x] ALTER OPERATOR
      • [x] ALTER PACKAGE
      • [x] ALTER PROCEDURE
      • [x] ALTER PROPERTY GRAPH
      • [x] ALTER SEQUENCE
      • [x] ALTER SYNONYM
      • [x] ALTER TABLE
      • [x] ALTER TABLESPACE
      • [x] ALTER TRIGGER
      • [x] ALTER TYPE
      • [x] ALTER USER
      • [x] ALTER VIEW
    • [x] DROP ... IF EXISTS
      • [x] DROP ANALYTIC VIEW
      • [x] DROP ATTRIBUTE DIMENSION
      • [x] DROP CLUSTER
      • [x] DROP DATABASE LINK
      • [x] DROP DIRECTORY
      • [x] DROP DOMAIN
      • [x] DROP EDITION
      • [x] DROP FUNCTION
      • [x] DROP HIERARCHY
      • [x] DROP INDEX
      • [x] DROP INDEXTYPE
      • [x] DROP INMEMORY JOIN GROUP
      • [x] DROP JAVA
      • [x] DROP LIBRARY
      • [x] DROP MATERIALIZED VIEW
      • [x] DROP MATERIALIZED VIEW LOG
      • [x] DROP MATERIALIZED ZONEMAP
      • [x] DROP MLE ENV
      • [x] DROP MLE MODULE
      • [x] DROP OPERATOR
      • [x] DROP PACKAGE
      • [x] DROP PROCEDURE
      • [x] DROP PROPERTY GRAPH
      • [x] DROP SEQUENCE
      • [x] DROP SYNONYM
      • [x] DROP TABLE
      • [x] DROP TABLESPACE
      • [x] DROP TRIGGER
      • [x] DROP TYPE
      • [x] DROP TYPE BODY
      • [x] DROP USER
      • [x] DROP VIEW
• [x] Annotations
  • [x] annotations_clause
  • [x] constraint (cannot specified)
  • [x] ALTER DOMAIN
  • [x] ALTER INDEX
  • [x] ALTER MATERIALIZED VIEW
  • [x] ALTER TABLE
  • [x] ALTER VIEW
  • [x] CREATE DOMAIN
  • [x] CREATE INDEX
  • [x] CREATE MATERIALIZED VIEW
  • [x] CREATE TABLE
  • [x] CREATE VIEW
  • [x] check more objects such as types, packages, functions, procedures (cannot specified)
• [x] JSON-Relational Duality View
  • [x] CREATE JSON RELATION DUALITY VIEW
  • [x] DROP VIEW (also applicable for JSON RELATIONAL DUALITY VIEW)

New SQL Features not mentioned in SQL Language Reference 23c

• [x] New Operators
  • [x] SHARD_CHUNK_ID
  • [x] FUZZY_MATCH
  • [x] PHONIC_ENCODE
• [x] New Conditions
  • [x] BOOLEAN Test Condition
  • [x] JSON Schema: IS JSON Condition
• [x] Table Value Constructor
  • [x] Select
  • [x] Insert
  • [x] Merge
• [x] New Functions
  • [x] BOOLEAN_AND_AGG
  • [x] BOOLEAN_OR_AGG
  • [x] DOMAIN_CHECK
  • [x] DOMAIN_CHECK_TYPE
  • [x] DOMAIN_NAME
  • [x] DOMAIN_DISPLAY
  • [x] DOMAIN_ORDER
  • [x] ROUND_TIES_TO_EVEN (introduced in 18c)
  • [x] SYS_ROW_ETAG
  • [x] TO_BOOLEAN
• [x] Extended Functions
  • [x] CAST (domain, validate/novalidate)
  • [x] JSON_ARRAY (simplified syntax using JSON [] and [] in nested context, ...)
  • [x] JSON_OBJECT (simplified syntax using JSON {} and {} in nested context, ...)
  • [x] JSON_OBJECTAGG (strict, with unique keys)
  • [x] JSON_QUERY (type strict/lax, on_mismatch_clause)
  • [x] JSON_SCALAR (null on error, error on error)
  • [x] JSON_SERIALIZE (ordered)
  • [x] JSON_TABLE (type strict/lax)
  • [x] JSON_TRANSFORM (type strict/lax, operations: sort, nested path, case, copy, intersect, merge, minus, prepend, union)
  • [x] JSON_VALUE (json_passing_clause)
• [x] Silently Decommissioned Functions (not in 23c docs anymore, deprecated, can be found in 21c docs, still part of the grammar)
  • [x] APPENDCHILDXML
  • [x] INSERTCHILDXML
  • [x] INSERTCHILDXMLAFTER
  • [x] INSERTCHILDXMLBEFORE
  • [x] INSERTXMLAFTER
  • [x] INSERTXMLBEFORE
  • [x] UPDATEXML
• [x] New Statements
  • [x] ALTER PMEM FILESTORE (introduced in 21c)
  • [x] CREATE LOGICAL PARTITION TRACKING
  • [x] CREATE PMEM FILESTORE (introduced in 21c)
  • [x] DROP PMEM FILESTORE (introduced in 21c)

[PhilippSalvisberg]

closed with commit 2a0ed170a07f4e9ef3e48fb7fa7024bed2cc30cf on Azure DevOps