Wednesday, May 6, 2015

Rdbms -- Generating output HTML reports using SQLPLUS

Just a little reminder for the ones who already know, and a useful feature for the ones who has never used it before.
We can generate Html formatted outputs using sqlplus..
This feature eases our job for producing detailed environment reports in a more readible form..
Using html formatted sqlplus outputs we can scriptize our control scripts or even our health check reports , thus expend less energy while dealing with them..

Oracle uses this to gather info for its components in a formatted manner using this feature(Ref: How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1? (Doc ID 470211.1) ).. Why not we?

Here is an example;

Connect to the database using sqlplus

sqlplus apps/apps
SQL>
--Copy & Paste the following:
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON  -
HEAD "<TITLE>Database Parameters Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFF6} --> -
</STYLE>" -
BODY "TEXT='#000AFF'" -
TABLE "WIDTH='95%' BORDER='4'"
set pagesize 1000
set linesize 1000
set feedback off
set serveroutput off
spool erman.html
select * from v$parameter;
spool off

After that, execute exit; command.


Here is the HTML formatted output file.. It displays the contents in v$parameter view in this example..


No comments :

Post a Comment