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.
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.
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.
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.
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.
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;
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.