DEV Community

Loïc
Loïc

Posted on

3 2

Reusable SQL templates: learn about SQL Macros🤯

SQL Table Macro

👉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
Enter fullscreen mode Exit fullscreen mode

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!

Second version

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!

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay