The power of SQL macros

Here is another example of what I’m sure will just become a plethora of such examples from the community on how the flexibility of SQL macros can solve problems that would normally take a lot of code in the form of DBMS_SQL and/or object types and/or pipelined functions and/or …well, you get the idea

A few days back someone asked me if any of our tools had the facility where the output from a SQL query could be represented as a HTML table in the most simplest HTML possible so that it can be cut-pasted into a HTML editor. Recent versions of SQL*Plus comes pretty close to achieving this with the “set markup html” option – here’s an example of what it will output:

But not everyone uses SQL Plus, and you don’t have an enormous amount of control over the HTML generated because its aimed at producing a report style output.

Which got me thinking: “Wouldn’t it be nice to take an query and produce the most basic HTML table output as a utility?”

Inspired by Andrej Pashchenko‘s blog post on using SQL macros to generated CSV output, here’s a similar routine to generate a HTML table for any passed query.

Apologies for posting the code as an image but if you’ve ever tried to post snippets that contain a truck load of HTML tags, then you’ll understand . But I’ll also post this on my github repo for you to grab, and of course you’re free to edit it (in particular for date formats etc) to suit whatever need you have.

Thus you simply construct any input you like using a WITH clause, and out will pop a nice clean HTML table.

Yet another motivation to learn more about SQL macros.