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.

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/report_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.