HTML reports using SQL*Plus

Using SQL*Plus it is possible to create presentable HTML reports that improve readability over traditional ASCII based reports.

A Simple report:

set markup html on 
set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off

spool currentTime1.html

select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') "Current Time" from dual
/

spool off

Output:

Current Time
06.JUN.2012 18:56:29

Taking a plain report and adding color to it and stylesheet:

spool currentTime2.html

set markup html on spool on entmap off -
 head ' -
 ' -
 body 'text=black bgcolor=fffffff align=left' -
 table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'

select 
'

' || to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') ||'
' "Current Time" from dual
/

spool off

Output:

Current Time
06.JUN.2012 20:56:03

Color highlighting columns in the output:

set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off

set markup html on spool on entmap off -
    head ' -
    ' -
    body 'text=black bgcolor=fffffff align=left' -
    table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'

define COLOR_BEGIN_GREEN = '';
define COLOR_END_GREEN = '';

spool userenv.html

select 
 sys_context('USERENV','SESSION_USER') "Session User",
 ''|| sys_context('USERENV','INSTANCE_NAME')|| 
 '' "Instance Name",
 '&COLOR_BEGIN_GREEN' || sys_context('USERENV','SERVER_HOST') ||
 '&COLOR_END_GREEN' "DB Hostname"
from dual
/

spool off

Output:

Session User	Instance Name	DB Hostname
SYS	lexus	jupiter

Displaying columns in different colors based on a condition. This can help highlighting an issue in a report:

set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off

set markup html on spool on entmap off -
    head ' -
    ' -
    body 'text=black bgcolor=fffffff align=left' -
    table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'

define BEGIN_WARNING = '';
define END_WARNING = '';

define BEGIN_OK = '';
define END_OK = '';

column server_host new_value server_host
column instance_name new_value instance_name

select 
 sys_context('USERENV','SERVER_HOST') server_host,
 sys_context('USERENV','INSTANCE_NAME') instance_name
from dual
/

spool logMode.html

prompt 
&server_host..&instance_name - ARCHIVELOG MODE
select 
decode(log_mode,'NOARCHIVELOG','&BEGIN_WARNING'|| log_mode||'&END_WARNING',
                '&BEGIN_OK' || log_mode||'&END_OK') 
"

Archive Log Mode
" from v$database
/

spool off

Output:

jupiter.lexus - ARCHIVELOG MODE

Archive Log Mode
NOARCHIVELOG

Putting it all together, show init.ora prameters and comment, highlight problem areas in red otherwise in green:

set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off

define BEGIN_WARNING = '';
define END_WARNING = '';

define BEGIN_OK = '';
define END_OK = '';

column server_host new_value server_host
column instance_name new_value instance_name

select 
 sys_context('USERENV','SERVER_HOST') server_host,
 sys_context('USERENV','INSTANCE_NAME') instance_name
from dual
/

set markup html on spool on entmap off -
    head ' -
    ' -
    body 'text=black bgcolor=fffffff align=left' -
    table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'

spool initParameters.html

prompt 
&server_host..&instance_name - INIT.ORA Parameters
select 
name "Parameter Name",
value "Parameter Value",
comments "Comments"
from
(
select 
name name,
decode(value,null,'',value) value,
case
--
-- control_file_record_keep_time
--
   when name = 'control_file_record_keep_time'
   then 
 case
        when value <=14 then
 '&BEGIN_WARNING Value of '||value||
 ' potentially low for retention of RMAN backup information &END_WARNING'
 end
--
-- destinations *dest*, dump and log destinations
--
   when name like '%dest%'
   then
 case when lower(value) like '%product%'  
 then
 '&BEGIN_WARNING Destination possibly under the Oracle product directory '||value||
 ' &END_WARNING'
 when lower(value) like '%temp%' or  lower(value) like '%tmp%'
 then
 '&BEGIN_WARNING Destination possibly under a temporary directory!'||value||
 ' &END_WARNING'
 end
--
-- processes
--
   when name = 'processes'
   then
 case 
 when value < 100
 then
 '&BEGIN_WARNING '||name||' value '||value||
 ' may be too low, see v$resource_limit max utilisation &END_WARNING'
 end
--
-- skip_unusable_indexes
--
   when name = 'skip_unusable_indexes'
   then
 decode(value,'FALSE',value, '&BEGIN_WARNING '||value||' &END_WARNING')
--
-- SPFILE
--
   when name = 'spfile'
   then
 decode(value,'?', '&BEGIN_WARNING No SPFILE set &END_WARNING', 
     '&BEGIN_OK SPFILE in USE &END_OK')
--
-- undo management
--
   when name = 'undo_management'
   then
 case when lower(value) != 'auto'
 then
 '&BEGIN_WARNING UNDO_MANAGEMENT is not AUTO '|| value ||
 ' &END_WARNING'
 end
end comments
from v$parameter, v$instance
)
where comments is not null
order by name, value
/

spool off

jupiter.lexus - INIT.ORA Parameters

Parameter Name	Parameter Value	Comments
control_file_record_keep_time	7	Value of 7 potentially low for retention of RMAN backup info rmation
skip_unusable_indexes	TRUE	TRUE
spfile	/app/oracle/product/11.2.0/db_1/dbs/spfileABC.ora	SPFILE in USE

References:
Originally inspired from:
Creating HTML Reports with SQLPLus