Reusable SQL templates: learn about SQL Macros🤯

👉SQL Table Macro: create reusable SQL templates (FROM clause) you can pass tables, and other parameters to at runtime.

Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/sql-macros-sqm.html

SQL Table Macro is a 21c feature backported to the 19.7 release update.

CREATE TABLE planets (
    json_document BLOB,
    CONSTRAINT json_document_is_json CHECK (json_document IS JSON)
);

INSERT INTO planets (json_document) 
VALUES ( '[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, 
            {"name":"Mars"}, {"name":"Jupiter"}, {"name":"Saturn"}, 
            {"name":"Uranus"}, {"name":"Neptune"} ]' );
COMMIT;

-- One row retrieved containing a JSON array
SELECT JSON_SERIALIZE(json_document) AS array FROM planets;

                                                       ARRAY
-------------------------------------------------------------
[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, … ]


-- Second version where the column table is also a parameter
CREATE OR REPLACE FUNCTION unwind(t DBMS_TF.TABLE_T, c DBMS_TF.COLUMNS_T) 
       RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN := q'{
      SELECT d.array_item AS JSON_DOCUMENT, d.order_id
        FROM unwind.t nested }'|| unwind.c(1) ||q'{ COLUMNS (
                 NESTED PATH '$[*]' COLUMNS (
                     array_item CLOB FORMAT JSON PATH '$',
                     order_id FOR ORDINALITY
                 )
             ) d}';
END;
/
-- Using a table and one of its columns as parameters of SQL Macro
SELECT * FROM unwind( planets, COLUMNS( json_document ) );         

     JSON_DOCUMENT   ORDER_ID
------------------ ----------
{"name":"Mercury"}          1
  {"name":"Venus"}          2
  {"name":"Earth"}          3
   {"name":"Mars"}          4
{"name":"Jupiter"}          5
 {"name":"Saturn"}          6
 {"name":"Uranus"}          7
{"name":"Neptune"}          8

The example above uses the second version of my SQL Macro (compared to the animated GIF above) where I can also pass a column as a parameter!

Remark for Pluto: according to NASA, this is no more considered as the 9th planet 🙂

Kudos to Chris Saxon for helping a lot finding the summary for such a great feature!