New Features in Oracle Database 12.2 (Oracle SQL and PL/SQL Improvements)

Enhancing CAST Function With Error Handling

The existing CAST function is enhanced to return a user-specified value in the case of a conversion error instead of raising an error.
CAST lets you convert built-in data types or collection-typed values of one type into another built-in datatype or collection type. Previously it returned an error if the conversion is not supported. Now it returns the default value which is defined by the user.

Eg: - 

SELECT CAST('N/A' AS NUMBER DEFAULT '0' ON CONVERSION ERROR) FROM DUAL;

New SQL and PL/SQL Function VALIDATE_CONVERSION

The new function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type.

Eg: - 

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE) FROM DUAL;

Enhancing LISTAGG Functionality 

LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality is added for managing situations where the length of the concatenated string is too long.

Eg:- 

SELECT col_1, LISTAGG(col_2, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY col_2) AS students
FROM   table;

Approximate Query Processing

This release extends the area of approximate query processing by adding approximate percentile aggregation. With this feature, the processing of large volumes of data is significantly faster than the exact aggregation. This is especially true for data sets that have a large number of distinct values with a negligible deviation from the exact result.
Approximate query aggregation is a common requirement in today's data analysis. It optimizes the processing time and resource consumption by orders of magnitude while providing almost exact results. Approximate query aggregation can be used to speed up existing processing.

Materialized Views: Real-Time Materialized Views

Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables and are considered stale. Using materialized view logs for delta computation together with the stale materialized view, the database can compute the query and return correct results in real time.

For materialized views that can be used for query rewrite all of the time, with the accurate result being computed in real time, the result is optimized and fast query processing for best performance. This alleviates the stringent requirement of always having to have fresh materialized views for the best performance.

Materialized Views: Statement-Level Refresh

In addition to ON COMMIT and ON DEMAND refresh, the materialized join views can be refreshed when a DML operation takes place, without the need to commit such a transaction. This is predominantly relevant for star schema deployments. 
The new ON STATEMENT refresh capability provides more flexibility to the application developers to take advantage of the materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.