Sqlplus output at the next level



A while ago i posted a note about generating very simple html reports based on a sheel script running a sqlplus command through the cgi functionality in apache (it's honestly very simple - it just doesnt sound it when you say it like that.

the original post is here: http://dbaharrison.blogspot.co.uk/2013/04/very-simple-cgi-page-to-run-sqlplus.html

I wanted to revisit it to show the things that can be done to make the output look nicer using simple style sheets to format the code.

Using the base sql file i used in the original example a small bit of extra code needs to be added


The 'extra' part is this (which must be before the set markup html on line)

prompt -
     <style type="text/css"> -
        table { background: #eee; font-size: 90%; } -
        th { background: #ccc; } -
        tr:nth-child(odd) { background-color: #CCE6FF ;} tr:nth-child(even) { background-color: LightGray;} -
        tbody tr:hover { background: yellow;} -
        td { padding: 0px; } -
     </style>


All the code is actually doing is making use of the prompt keyword to output the code for the stylesheet. This will simply 'echo' what is typed into the resulting outputted html code.

The dashes (-) at the end of the lines are just continuation characters in sqlplus - this looks nicer than having the whole thing in one long line.

The stylesheet as well as donig some basic formatting also does a couple of clever tricks to make things look nice.

the tr: line sets a different background coloud depending if the row in the html table is odd or even making the results look more aesthetic.

the tbody tr:hover line changes the whole row to yellow when the mouse hovers over it.

And here is what the end result looks like - i was hovered over the last row when i took the screen grab.

This is all very simple and you can find loads of examples of css formatting of html tables on the internet.

You can start doing even more clever things with sql generating html in the select statements and do conditional formatting, if you're going that far though you probably should be using a proper reporting tool......

Comments