With JSON_TRANSFORM you can apply a series of transformation operations, step by step changing pieces of the JSON into what you want, without touching other parts of the JSON.

UTIL_CALL_STACK provides an interface for the call stack, backtrace stack and error stack for Pl/SQL program execution.  

It is important to understand the difference between the call stack dynamic depth, the error stack error_depth and the backtrace backtrace_depth when looking for the original source of an error using UTL_CALL_STACK.

From 21c, the FOR loop allows you to specify an iterand as MUTABLE, meaning you can change it's value inside the loop. That gives you very detailed control of how the iterand is incremented, but at the same time opening up for potentially very convoluted logic, so be careful.

JSON_TABLE or NESTED can both be used to project rows from json arrays.

From version 23ai, you can use the FROM/USING clause to limit rows and/or provide source data from one or more other tables in an UPDATE statement.

Oracle 23ai introduced the SQL Transpiler, allowing simple PL/SQL functions to be converted to their SQL equivalents.  

The transpiled function then runs as pure SQL instead of executing in the PL/SQL engine.

This reduces the context switching overhead of calling user defined functions from SQL as long as the Transpiler restrictions are met by the function.

Oracle Database 23ai added enumeration (enum) domains. When you associate an enum with a column, the database adds a constraint that only allows you to add values from the enum. You can insert the values themselves or referencing the associated enum names.

Loop iteration control enhancements introduced in Oracle 21c allow for more complex logic than simply stepping though a range of numbers.

Using TO_CHAR or CAST to convert a BOOLEAN to a string can only result in the values TRUE and FALSE, there are no format model options to choose different ways of converting.

From Oracle Database 21c you can initialize arrays using cursors in qualified expressions (type constructors). You can assign columns from the cursor to the indices of the array.

In 21c, the values of and pairs of iteration controls were added to allow iterating a collection by a value iterand.  

The value iterand is not a reference to the collection that is used by the iteration control to generate the sequence of iterands.

Modifications of the value iterand are not reflected in the collection object.

The is_Date member function of JSON_ELEMENT_T only returns true if the JSON was constructed manually by PUT'ting a DATE datatype into the JSON. If the JSON_ELEMENT_T stems from parsing a JSON string, then is_Date returns false even if the element has a value that matches the ISO 8601 date formatting.

From Oracle Database 23ai, the Oracle optimizer can rewrite a join query to use any equivalent materialized view. This can happen regardless of whether the syntax uses Oracle or ISO style joins.

Using JSON_TRANSFORM Set Operations introduced in Oracle 23ai create arrays where the array order is indeterminate.  The SORT operation has to be used to create a determinate array order.

In version 23ai, you can ROUND interval datatypes - before that you would have to transform the interval to something numeric and round the number instead.

The SQL Transpiler can in some cases automatically do what you yourself would have had to use a scalar SQL macro to do, namely eliminate PL/SQL function calls by injecting the equivalent SQL expression into the query, thereby removing the need for context switching between SQL and PL/SQL engine.

In 23ai the PL/SQL JSON_VALUE operator returning clause can now accept a PL/SQL aggregate type name.

JSON Flex Columns allows a duality view to accept JSON documents containing attributes that do not map to relational columns and store those unknown attributes as JSON in a single JSON column in the relational table, providing a lot of flexibility for the application developers.

Oracle 23ai adds support for returning OLD and NEW values from UPDATE DML and from MERGE statements.

The FIXED_DATE system option can be set during the execution of testing code to return repeatable results  when the code being tested uses SYSDATE.

In a column of datatype BOOLEAN you can insert many different expressions that in one form or other implicitly convert to boolean.

ENUM domains introduced in 23ai support the use of multiple aliases for each enum value.  

Scalar macros are not simply a matter of text substitution, they need to represent scalar expressions, so you cannot use aggregate functions in the SQL text returned by a scalar macro.