Release 21c of Oracle AI Database extended for loops in PL/SQL. New options include:

• by - specify a custom increment
• when <condition> - only run the loop body if the condition is true
• A comma-separated list of values to loop through

Oracle AI Database added the fuzzy_match operator. This supports several fuzzy matching algorithms:

• LEVENSHTEIN corresponds to UTL_MATCH.EDIT_DISTANCE or UTL_MATCH.EDIT_SIMILARITY and gives a measure of character edit distance or similarity.
• DAMERAU_LEVENSHTEIN distance differs from the classical LEVENSHTEIN distance by including transpositions among its allowable operations in addition to the three classical single-character edit operations (insertions, deletions and substitutions).
• JARO_WINKLER corresponds to UTL_MATCH.JARO_WINKLER (a percentage between 0-1) or UTL_MATCH.JARO_WINKLER_SIMILARITY (the same but scaled from 0-100).
• BIGRAM and TRIGRAM are instances of the N-gram matching technique, which counts the number of common contiguous substrings (grams) between the two strings.
• WHOLE_WORD_MATCH corresponds to Word Match Percentage or Count comparison in Oracle Enterprise Data Quality. It calculates the LEVENSHTEIN or edit distance of two phrases with words (instead of letters) as matching units.
• LONGEST_COMMON_SUBSTRING finds the longest common substring between the two strings.

From release update 23.8, the optimizer can gather dynamic stats for table function calls. This can give better row estimates. 

You can control this with:

• The global PL/SQL dynamic_stats preference
• The dynamic_stats function preference
• The parameter plsql_function_dynamic_stats

The priority of these (from highest to lowest) is:

plsql_function_dynamic_stats > Function preference > Global preference

If you need to create JSON with empty strings ("") from SQL values, that will not happen by default, as an empty string in SQL is actually a NULL and will produce a JSON null. But in JSON creation functions you can specify EMPTY STRING ON NULL to make SQL NULL turn into JSON "" empty string

The time_bucket function find the start or end times of an N-unit duration for a datetime. This was added in release 23.7.

The syntax for this is:

time_bucket ( <datetime>, <stride>, <origin> )

There is an optional fourth parameter, which takes the values start or end.

The data type of the first and third arguments must be identical. The second argument can be either a:

• An interval day to second or interval year to month literal
• A call to numto*interval function
• An ISO 8601 time unit string