SQLPLUS HTML COLOR

Recently i was asked to generate a report that shows all the databases that were backed up via RMAN and they wanted a color code when a backup failed or wasn’t completed successfully.

That posed a challenge and took some research, but finally generated a report.   Below is a sample report and how to color code on afailed backup, but can be used on any sql report:


SET MARKUP HTML ON SPOOL ON
SET TERMOUT OFF
SET PAGESIZE 1000
SET LINESIZE 300
SET TRIMOUT ON
SET TERMOUT ON
set pages 999
set feedback off
break on db_name page
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
spool backup_report
SET MARKUP HTML ENTMAP OFF
PROMPT <H2><center><b> RMAN Backup  details  </b></center></H2>
SET MARKUP HTML ENTMAP ON
SET VERIFY    off
SET lines 132 pages 9999 feedback off
COLUMN start_time      format date              heading ‘Start Date’
COLUMN end_time      format date              heading ‘End Date’
COLUMN input_bytes format 999,999,999,999 heading ‘Input Bytes’
COLUMN output_bytes        format 999,999,999,999 heading ‘Output Bytes’
COLUMN cstatus format a40  heading ‘Status’
COLUMN object_type              format a10  heading ‘Backup Type’
COLUMN time_taken_display      format a40  heading ‘Time Taken’
COLUMN cstatus ENTMAP OFF
select db_name, object_type,start_time,end_time,input_bytes, output_bytes,
(case
when status like ‘%WARN%’ then ‘<font color=”green”>COMPLETED</font>’   <==== Note color default for non-failed jobs
when status like ‘COMPLETED’ then ‘<font color=”green”>COMPLETED</font>’
ELSE ‘ <font color=”red”>’||status||'</font>’                                                             <=== Note – falls through code and any noncompleted status uses this color
END) cstatus
from <RMAN CATALOG>.rc_rman_status
where object_type = ‘DB FULL’
and operation not in (‘RESTORE VALIDATE’)
order by db_name,start_time Asc;
SET MARKUP HTML ENTMAP OFF
spool off

Hope this helps with your reports and can be used for any type of report that requires color coding.

–Mike

Advertisements

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s