Generating HTML reports from Oracle database via SQLPLUS

The SQL*Plus command-line interface enables us to generate either a complete web page, or HTML output which can be embedded in any web page by using  SQLPLUS Command -MARKUP "HTML ON" or SET MARKUP HTML ON SPOOL ON to produce complete HTML pages  with <HTML> and <BODY>  tags.

By defining a CSS “Cascading Style Sheet” in the SQL we can define how to display the HTML elements that were generated from MARKUP embedded SQL.   

SET MARKUP HTML ON—Requests output in HTML form. Detail rows, and column headings, will be written to an HTML table.

HEAD—Specifies content to be written into the HTML <table> tag, and uses the css style sheet  to define the properties for the table/output.

HEAD "<style type='text/css'>” -

ENTMAP OFF—Lets HTML  tags embedded in the spool file such as signs ‘<’ ‘>’ ‘&’.

[oracle@localhost dbareports]$ cat   invalidobj.sh

#!/bin/bash

ORACLE_SID=ORCL; export ORACLE_SID

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome; export ORACLE_HOME

PATH=$PATH:$ORACLE_HOME/bin; export PATH

NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT

TIMESTAMP=`date +%Y_%m_%d`

DATE=`date +%Y-%m-%d`

HOST=localhost

##

FILE=/home/oracle /dbareports/DB_INVALID_OBJECTS_$ORACLE_SID.html

sqlplus -S scott/tiger@ORCL <<EOF

spool $FILE

set head off

set feedback off

set markup HTML ON

--

TTITLE -

BOLD LEFT "<b>Report:</b>Database Invalid  Objects" -

BOLD CENTER  "<b>Last Run Time:</b>" _DATE -

BOLD RIGHT "<b>Page:</b>" SQL.PNO  skip 2  

--

@/home/oracle/dbareports/invalidobj.sql $FILE

--

exit

EOF

##

The above shell script calls the invalidobj.sql script, where the markup html code is embedded inside it.

[oracle@localhost dbareports]$ Cat invalidobj.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 { -

font:10pt Arial,Helvetica,sans-serif; -

text-align:left; -

color:Black; background:white; -

padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -

tr { -

font:10pt Arial,Helvetica,sans-serif; -

text-align:left; -

color:Black; background:white; -

padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -

tr:nth-child(odd) { -

font:10pt Arial,Helvetica,sans-serif; -

text-align:left; -

color:Black; background:#ffffff; -

padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -

tr:nth-child(even){ -

font:10pt Arial,Helvetica,sans-serif; -

text-align:left; -

color:Black; background:#dddddd; -

padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -

th { -

font:bold 10pt Arial,Helvetica,sans-serif; -

color:#336699; -

background:#cccc99; -

border-style: solid; -

padding:0px 0px 0px 0px;} -

h1 { -

font:bold 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>Your ORG Database Reports</title>" 

TABLE "border='1' width='90%' align='center'" -

ENTMAP OFF SPOOL ON

--

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH:MI AM';

--

set HEAD ON

--

spool &1 append

--

TTITLE -

BOLD LEFT "<b>Invalid Schema Objects by Type</b>" skip2     

--

break on owner

set pages 400

--

select owner, object_type, count(*) Invalid from dba_objects

where status='INVALID'

group by owner, object_type

union ALL

select NULL owner, 'TOTAL:  ' object_type, count(*) Invalid from dba_objects

where status='INVALID'

order by 1;

--

spool off

--