From version 23.26.1 there are a lot of functions available to make it easier to work with calendars, including fiscal calendars.

As of Oracle AI Database 23.26.2, subquery factoring clauses can be nested with some restrictions.  

Set operations (UNION, MINUS, INTERSECT) and recursive subqueries are both restricted.

It is possible to workaround the set operator restriction using inline views.

It's often a good idea to label your loops, particularly if they're nested and/or much code within the loop. You can use the label in EXIT statements.

You can also make your code more readable by specifying the loop label in the END LOOP statement so you can see which loop it is that ends right here in the code. However, that is just for readability, the compiler does not check whether you specify or correct loop label or not in END LOOP.

JSON Path conditional expressions using case and decode were introduced in Oracle AI Database 26.2

You can help yourself avoid some potential bugs by setting your database to throw a compile time error if you have a WHEN OTHERS exception block in your code that does not re-raise an error.

But what do you then do for those very rare cases where it is actually on purpose to have a WHEN OTHERS without a RAISE, if the database then throws an error? You can use PRAGMA SUPPRESSES_WARNING_6009 to create a procedure or function that the compiler will accept as an alternative to RAISE or RAISE_APPLICATION_ERROR.

Function DATEDIFF give us an easy way to determine number of years, months, hours (or basically any time unit) between two dates.

System time functions in an SQL query will always return the time when the query began executing.

To report row by row timing information for a time intensive process in SQL it is necessary to leverage PL/SQL functionality.

The Hamming vector distance metric is available in PL/SQL, but only as a metric specification for the VECTOR_DISTANCE function.  

The HAMMING_DISTANCE function is not supported in PL/SQL for comparing binary vectors.

A property graph can be used as an alternative to hierarchical queries. If you wish, you can setup duplex edges, which can make your code more self-documenting.

Filtered Aggregates, introduced in 26.1 simplify the syntax required to aggregate different elements of groupings independently.

Just like we are used to using HAVING clause like a “where” clause on the results of aggregate functions, from version 23.26.0 we can use the QUALIFY clause like a “where” clause on the results of analytic functions.

ISO week numbers are not always sequential week numbers.  A simple user request can have hidden complexity that requires analytics to solve.

Using boolean expressions can both simplify and clarify your code.

A collection can be populated using a cursor iteration control as a qualified expression.  If the iteration control is based on a weak cursor variable, the iterator type must be defined.

UTL_URL.ENCODE can URL encode a string in multiple ways - you need to think about the values you pass in to the parameters of the function.

Take particular note that the parameter for characterset behaves a bit different than many functions. You're probably used to passing a NULL behaves the same as not passing a value (ie. relying on the default.) This is not the case here, where a NULL means database character set, while the default is the characterset of UTL_HTTP.GET_BODY_CHARSET, which currently defaults to ISO-8859-1.

Oracle AI Database 23.26.1.0 Introduced support for filtered aggregates, simplifying aggregation of different elements within the same grouping.

UUID_TO_RAW provides an easy way to turn a UUID string into RAW, no matter which of the 4 supported syntaxes the string is using. If you don't want an error raised on invalid UUID values, test with IS_UUID before converting.

The IS [NOT] TRUE boolean test condition is only valid syntax in SQL.  In PL/SQL the same test has to use the equality operator.

Iteration controls allow us in many situations to create concise and simple logic without needing extraneous variables and code.

Creating a use case domain with a domain order function standardizes the ordering of queries on tables using the domain without duplicating the order clasue in each SQL statement.

CAST of a TIMESTAMP WITH TIME ZONE to a DATE (whether explicit or implicit) simply removes the time zone information from the value. If you instead want your DATE to contain the timestamp value with time zone converted to for example the database time zone, you need to first convert, then cast.

It is possible to pass multiple arguments in a single bind variable with the PASSING clause if the bind variable is using JSON datatype.

SQL Macros used in PL/SQL methods are resolved at compile time.

GraphQL is an alternative way to query data and retrieve JSON than using SQL. You can use the table function GRAPHQL to use GraphQL syntax within Oracle database.

Hidden and generated fields in version 23.6 gives you more flexibility for read-only attributes of JSON Relational Duality Views.

Introduced in 26ai, the QUALIFY clause allows for filtering a result set with an analytic function, or any aliased expression.

In 23ai, support for multiple arguments was added to the concat function.