What is new in Oracle 20c for developers – Part-1 New SQL features by Lakshman Bulusu

This blog post enumerates on some of the new features in Oracle 20c for developers. It describes these new features (with code snippets  for some of them) in two parts, namely, SQL and PL/SQL, for developers and application programmers. Part -1 outlines new SQL features and part-2 describes new PL/SQL features.

As a preview, the below shows the timeline of Oracle DB releases 11g thru 20c.

New SQL features in Oracle 20c

1. SQL Macros

As a new re-usability feature, SQL macro allows shared logic encapsulation into reusable, parameterized macros that can be used in other SQL statements. To make use of this feature, create a function with the SQL_MACRO(SCALAR) or SQL_MACRO clauses in its definition. This makes the function behave as a macro just like any other “macro”. The former creates a scalar macro (SCALAR type) that can be called in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses; and the latter creates a table macro (TABLE type) that can only be called in the FROM clause of a query. However, it can return a VARCHAR2 or CLOB value only. SQL Macros always run  as invoker right’s functions and need INHERIT PRIVILEGES to be granted to it by its owner. Virtual columns, MVs, function-based indexes, and editioning views cannot reference a SQL_MACRO function.

This adds a strong security layer for these macro-defined functions due to INHERIT privileges and invoker rights combination. SQL macros
also enhance code quality. And can be adopted as enterprise standards for SQL/PLSQL. Secondly, SQL_MACRO based functions avoid
context-switching between SQL and PL/SQL. Thirdly, SQL_MACRO exposes SQL code transparent to SQL optimizer thereby resulting in efficient
execution. Fourthly, SQL_MACRO supports cross-DB compatibility by way of mimicking code in a different database to be written in
equivalent Oracle SQL code but giving the ability to use it in the same manner as in the other DB.
Example 1:  Using SQL_MACRO(SCALAR)

CREATE FUNCTION module_long_text(p_cd VARCHAR2,
                                 p_descr VARCHAR2)
                     RETURN VARCHAR2 SQL_MACRO(SCALAR)IS
BEGIN
   RETURN q'{
          p_cd || '-' || p_descr)
          }';
END;
/
--This query shows the module_long_text SQL macro used in a scalar expression
SELECT module_id, module_long_text(p_cd => a.cd, p_descr => b.descr) module_tx
FROM module_tab a, lkup_tab b
WHERE a.cd = b.cd
ORDER BY 1;

Example2: SQL_MACRO as a TABLE macro

CREATE FUNCTION module_long_text(p_bus_grp_id VARCHAR2) RETURN VARCHAR2 SQL_MACRO(TABLE) IS 
BEGIN 
  RETURN q'{SELECT cd || '-' || descr 
                  FROM module_tab a, lkup_tab b 
                 WHERE a.cd = b.cd
                   AND a.bus_grp_id = module_long_text.p_bus_grp_id) 
                 }'; 
END; 
/

Example3: SQL_MACRO for mimicking SQL Server SUBSTRING function

CREATE FUNCTION substring(string VARCHAR2, start INTEGER, length INTEGER) RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS
BEGIN
  RETURN 'SUBSTR(string, start, length)';
END;
/

If you are using packaged functions as SQL_MACROs, each individual packaged functions must be defined as SQL_MACRO(SCALAR). In addition SCALAR MACROS can be overloaded.

2. Enhanced SQL operations including SET operations

• Cross-data type querying including NoSQL, JSON, XML, Graph, Spatial, and Files as well as transactions across all of these data types.
• MINUS [ALL], INTERSECT [ALL], and a new construct EXCEPT [ALL] that is the logical equivalent of MINUS [ALL] are introduced. This way complete ANSI SQL compliance is achieved that is much needed for migration and portability from other DB to Oracle. The below examples illustrate the use of EXCEPT and EXCEPT ALL.

Example 1: Example of using EXCEPT

SELECT a.emp_no, a.emp_name, a.hire_date, a.sal 
  FROM emp_lob a
 WHERE lob_id = 1
   AND hire_date BETWEEN ADD_MONTHS(SYSDATE, -6) AND ADD_MONTHS(SYSDATE, -1)
EXCEPT
SELECT a.emp_no, a.emp_name, a.hire_date, a.sql
  FROM emp_lob
 WHERE lob_id = 1
   AND TO_CHAR(hire_date,'MM') = TO_CHAR(SYSDATE,'MM');

The above SQL eliminates all employees in LOB 1 in the previous 5 months who are ALSO currently employed in LOB 1.
Example 2: Example of using EXCEPT ALL

SELECT a.emp_no, a.emp_name, a.hire_date, a.sal 
  FROM emp_lob a
 WHERE lob_id = 1
   AND hire_date BETWEEN ADD_MONTHS(SYSDATE, -6) AND ADD_MONTHS(SYSDATE, -1)
EXCEPT ALL
SELECT a.emp_no, a.emp_name, a.hire_date, a.sql
  FROM emp_lob
 WHERE lob_id = 1
   AND TO_CHAR(hire_date,'MM') = TO_CHAR(SYSDATE,'MM');

The above SQL returns all employees in LOB 1 in the previous 5 months who are NOT currently employed in LOB 1.

The INSERT ALL returns ALL rows in the first set that are there in the second set including duplicates across the columns selected.

3. High-performance SQL JSON data type (stored in binary format) and Autonomous JSON Database option in the Cloud

A new data type using the keyword JSON can be used in DDL statements such as CREATE TABLE or DMLs for JSON-enabled data. This is optimal for storing large JSON documents in-db and is optimized for query and DML involving JSON-type documents as well as for indexing. The JSON data type takes less storage than CLOBs and also resolves character-set differences (no character-set conversions are done). A number of JSON based functions in 20c allow declarative use with JSON column values to update, delete, or query such JSON column elements. JSON_TRANSFORM can be used in an UPDATE statement to set and remove JSON object elements. The below code snippets demonstrate this.

CREATE TABLE test_json (row_ky INTEGER PRIMARY KEY,
                             test_doc JSON);
SELECT tj.test_doc.<element1> FROM test_json tj;
UPDATE test_json
SET test_doc = JSON_TRANSFORM(test_doc, SET '$.element1.subelement1' = '<value>'
)
WHERE row_ky = <key-value>;

JSON data having scalar types like VARCHAR2, CLOB or BLOB can be converted to native JSON format using constructor JSON. Vice-versa JSON_SERIALIZE can be used to convert JSON native data to JSON scalar data.

A new addition to the Oracle cloud in 20c is the availability of Autonomous JSON Database (ADJ) in addition to Autonomous Data Warehouse and 
Autonomous Transaction Processing.

4. Analytic window function chaining

Oracle 20c enables window chaining when using analytic functions with the OVER clause. The window clause is specified as part of table expression and windowing functions use the window name thus specified. The below example illustrates this.

SELECT dept_no, hire_date, SUM (sal) OVER win,
                           COUNT (DISTINCT emp_no) OVER win 
  FROM emp
WINDOW win AS (PARTITION BY job ORDER BY hire_date);

The WINDOW …… AS clause is included as part of a table expression, with one window defined and reused multiple times. This reduces parsing effort as well as repetition of the OVER clause thus resulting in execution efficiency.

5. New CHECKSUM function

A SQL-native CHECKSUM function in Oracle 20c now allows calculating the hash value of a SQL expression. Instead of a making a PL/SQL call as in pre-20c using DBMS_SQLHASH.GET_HASH (to check integrity of result sets) or such other API (such as standard_hash(), dbms_crypto etc.), the CHECKSUM can be directly called in a SQL SELECT clause thus providing an easy and efficient way of preserving data integrity. Secondly, there is no need of any GRANTS to be given to use this function. The below example illustrates this.

  SELECT client_name, CHECKSUM(user_id||' '||user_full_name) as checksum_for_user
    FROM clients
GROUP BY client_name
ORDER BY client_name;

6. Enhanced machine learning and AI capabilities for both data scientists and non-ML users

These include the following:
a. New in-DB native new ML algorithms for deep learning model build and real-time scoring on new data such as XGBOOST algorithm that can be used for predictions like propensity to buy, and classification like text classification tasks; MSET-SPRT algorithm for anomaly detection.
b. Custom R and Python scripts to be run in-DB to augment these algorithms and can be deployed via REST API.
c. Auto-build of ML models for model selection, feature selection, and hyper-parameter tuning.
d. Recommendation of models for performance.