The power of SQL macros

Posted by

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 Smile

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:

image

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 Smile. 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.

image

 

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

image

Yet another motivation to learn more about SQL macros.

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.