Shape-Changing SQL

There’s static SQL and dynamic SQL, but in between there’s also SQL that’s “dynamic at parse-time” (but not at runtime) - also known as polymorphic (meaning it can change “shape”.) This talk looks at different ways to achieve this, from ODCI over polymorphic functions to SQL macros.

In PL/SQL on the one side we have static SQL that is plain fixed text in the source code and compiled together with the PL/SQL. On the other end of the spectrum we have dynamic SQL where a string containing SQL is built at runtime and parsed+executed with EXECUTE IMMEDIATE or DBMS_SQL.

In between those two ends, the spectrum over the versions are including more and more ways of doing SQL that is somewhere in-between; neither completely static in the source code, nor completely run-time dynamic. We can make SQL that is more or less dynamically built, but it is built during the hard parsing phase, leading to getting many benefits of static SQL along with some benefits of dynamic SQL. This can for various use-cases lead to better reusability of code in static SQL without necessitating dynamic SQL with its possible drawbacks. It can also sometimes be a way to expand the capabilities of SQL more performantly than PL/SQL functions.

Different methods exist in different versions for achieving such compile-time-dynamic SQL - ranging from Oracle Data Cartridge Interface (ODCI) over polymorphic table functions (PTFs) to SQL macros. This talk shows an overview of these methods, pros and cons, and when some methods are more applicable than others (and vice versa.)

This talk will take a look at:

• ODCI aggregate functions
• ODCI pipelined table functions
• Polymorphic table functions
• SQL scalar macroes
• SQL table macroes

It will discuss use cases of each, potential benefits, drawbacks and pitfalls, and how to examine the actual SQL produced.