A common criticism of SQL is it lacks composability - the ability to define small units you can combine into larger functions. Oracle Database addresses this with the introduction of SQL macros. These return SQL expressions, enabling you to define reusable components.
Using SQL macros you can SQL templates which you can pass tables, columns, and other values to at runtime. This enables you to build a library of functions to solve common problems, such as finding consecutive rows or the top-N rows per group.
This presentation explains how to use SQL macros.