Dec 24 2008

Generating HTML reports from SQL Plus

Category: SQLPlusittichai @ 3:13 am

Last week I was asked by one of IT managers on how to generate reports from database. He’d like to remind his direct reports on ticket’s backlog status. His requirement is just simply to run a simple SQL statement and email the result out weekly. I quickly told him that you can just cron or schedule the script. “Hold on! I’m not finished yet,” he said. If possible, he’d like to have some report data color-coded so everyone can be visually reminded of meeting or missing goals. “And lastly, if possible, report should be formatted nicely – not just in plain text,” he added.

Um…

This is the sample of report I tried to assist him. The required features would include a company logo, color-coded report data based on thresholds, and URL link on ID.

sample

To have the query result in the html format, fortunately, the SQL Plus provides the “markup html” feature since version 8.1.6. There have been a quite number of discussions about this already. But the best place for reference is at Oracle SQL Plus User’s Guide and Reference web site.

I started out with the current configuration of the markup html which by default is OFF.

SQL> show markup
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF

This looks like it is a lot, but actually it is just a bunch of CSS syntaxes which control how the data content to be formatted. If you re-arrange it, it should look like this –

markup HTML ON HEAD "<style type='text/css'> -
body { -
font:10pt Arial,Helvetica,sans-serif; -
color:black; background:white; } -
p { -
font:8pt Arial,sans-serif; -
color:grey; background:white; } -
table,tr,td { -
font:10pt Arial,Helvetica,sans-serif; -
text-align:right; -
color:black; background:white; -
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -
th { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:0px 0px 0px 0px;} -
h1 { -
font:16pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
border-bottom:1px solid #cccc99; -
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 { -
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
margin-top:4pt; margin-bottom:0pt;} -
a { -
font:9pt Arial,Helvetica,sans-serif; -
color:#663300; -
background:#ffffff; -
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style> -
<title>SQL*Plus Report</title>" -
BODY "" -
TABLE "border='1' width='90%' align='center' -
summary='Script output'" -
SPOOL OFF ENTMAP ON PREFORMAT OFF

Note that I added “-” (hyphen) so the tool will treat the next line as the continued line.

To make the query result to be in the html format, the “markup html” must be enabled – MARKUP HTML ON. Also, I disabled the ENTMAP to be OFF, so the SQL Plus won’t replace special characters <,>, ” and & with HTML entitles &lt;, &gt;, &quot; and &amp; respectively. This is needed so I can use the HTML tags in SQL statement. This could be done at the column level as well.

So this is the updated version with a “set” syntax to be ran at the SQL prompt –

$ cat /scripts/set_markup.sql
set markup HTML ON HEAD "<style type='text/css'> -
body { -
font:10pt Arial,Helvetica,sans-serif; -
color:blue; background:white; } -
p { -
font:8pt Arial,sans-serif; -
color:grey; background:white; } -
table,tr,td { -
font:10pt Arial,Helvetica,sans-serif; -
text-align:right; -
color:Black; background:white; -
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -
th { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:0px 0px 0px 0px;} -
h1 { -
font:16pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
border-bottom:1px solid #cccc99; -
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 { -
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
margin-top:4pt; margin-bottom:0pt;} -
a { -
font:9pt Arial,Helvetica,sans-serif; -
color:#663300; -
background:#ffffff; -
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
.threshold-critical { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:red; } -
.threshold-warning { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:orange; } -
.threshold-ok { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:green; } -
</style> -
<title>SQL*Plus Report</title>" -
BODY "<img src=http://www.acme.com/company_logo..gif/>" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON

For ease of execution, I placed above syntax in a SQL file named /scripts/set_markup.sql which will be called when report runs.

I added new classes (“.threshold-XXX”) so different texts can be displayed into different colors based on defined thresholds. I also added the company logo’s image on report under BODY section.

Please note that you can remove unneeded CSS formats, i.e., a{} or margins, to make script cleaner. However, for a demonstration purpose, I leave everything as is.

The report part /scripts/run_weekly_backlog_report.sql is something like this –

$ cat /scripts/run_weekly_backlog_report.sql

connect user/password

@/scripts/set_markup.sql

set pages 100
TTITLE LEFT _DATE CENTER '<h1>Weekly Top Backlog Report : Database Team NA Region</h1>' -
RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER '<h2>Red = Backlog > 30 days, Orange > 10 days</h2>'

spool d:\temp\backlog_report.htm

select '<a href="http://www.acme-intra.com/pls/apex/f?p=100:3:::::PID:'||ID||'">'||ID||'</a>' ID,
DESCRIPTION, ASSIGNED_TO, OPEN_DATE,
CASE
when SYSDATE-OPEN_DATE > 30 then
'<span class="threshold-critical">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
when SYSDATE-OPEN_DATE > 10 then
'<span class="threshold-warning">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
ELSE
'<span class="threshold-ok">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
END BACKLOG
from TBL_TICKETS
where STATUS='Open'
order by SYSDATE-OPEN_DATE DESC;

spool off

exit

This will execute “set_markup.sql” first to enable markup html. For the rest, there is nothing unusual. The ID column is embedded with an URL, so users can view the content of the ticket if needed. Also please note the use of the <h1>, <h2> and <span class> tags to format data based on defined thresholds.

This script can be called just by using

sqlplus /nolog @/scripts/run_weekly_backlog_report.sql

The last thing is just put above syntax in a batch file and add sending email (using mail or blat for Windows) with the content of the spooled html file.

Tags: , , , , ,

17 Responses to “Generating HTML reports from SQL Plus”

  1. John says:

    This is perfect script for what im looking to do. Thanks!
    I am trying to run multiple queries and output to one single html file. The Html works fine and create a separate table for each script that is called. But I am trying to figure out how to embed a separate TTITLE or h1 for each table. Any suggestions?
    thanks again.

  2. John says:

    Nevermind. I figured it out. Im going to place a TTITLE inside of each query script.

  3. Anand Doraiswamy says:

    Hello,
    Great article. I was raking my brain for 3 hrs and I finally got this. Bellisimo!!

    Anand

  4. Wickey says:

    Hi, it is a fantastic article, resolved a lot of my trouble. But unfortunately the IMG placement is not working out for me. When I open out put HTML file the IMG tag is placed in the BODY tag like this “<body >” (quotes are additional) I am using the above method exactly. Please help.

  5. Tariq says:

    Hi I am facing difficultes with IMG tag in the output HTML file it looks like this, thus not showing the image. Please help.

    <body >

  6. Tariq says:

    Hi, its a great article, just a small problem, as I am facing difficulties with following script,

    BODY “” – TABLE “border=’1′ width=’90%’ align=’center'” –

    When I open the HTML output file the above CSS style is translated as below, resulting in not showing the image, a kind help would be a log appreciated.

    <body >

  7. Takako Bister says:

    Great items from you, man. I have take note your stuff previous to and you are simply extremely fantastic. I really like what you’ve obtained here, really like what you are stating and the best way wherein you are saying it. You’re making it enjoyable and you continue to care for to stay it smart. I cant wait to read much more from you. This is actually a terrific site.

  8. Oracle DBA says:

    For IMG tag:
    just replace
    BODY “”
    with this:
    BODY “” at the end

  9. Saurabh Sharma says:

    Is there anything that can be done at row level, like coloring alternate rows in different color or different color based on a value in a column.

    • ittichai says:

      The different colors on the alternate rows can be done in CSS. When I google’d for “html css alternate color row”, I found couple web sites showing samples on how to do it.

      This should be similar to color the row based on a value. Basically the idea is to associate different colors based on different “class” label.

  10. prakum says:

    Hi,
    Nice tutorials. i have tried to use the above code, but i am getting below error, displaying like below:

    49.39
    Could you please help?
    Thanks

  11. Sqlplus HTML reports - AskDba.org Weblog | AskDba.org Weblog says:

    […] for application team to monitor AQ and wanted html formatting of table data. On searching I found this link from Ittichai Chammavanijakul  which makes use of css formatting to generate good visual […]

  12. Murali says:

    This is a great article, really useful, thanks for sharing.

  13. sureshreddy says:

    Hi ittichai,

    i am crearing a dashboard which will display the images but the page is public due to this the images are not displaying. can you please help me out. i am tried checking the blogs but it is not clear for me .

    Thanks
    sureshreddy

Leave a Reply