Oracle sqlplus output to html explained

Many times we require formatted sql reports or sql query outputs for our reporting needs and better readability. We had also similar requirement where we used to run cronjobs and needed the sql scripts like tablespace status etc. outputs in a good format via email to be shared to broader audience daily. So to achieve this better formatting Oracle provides options to generate html markup outputs which can be used to spool into a html file and can be used for reporting needs.
Here in “Oracle sqlplus output to html” article we will discuss about different options to take better formatted html markup outputs from sql files.

SET MARKUP HTML ON SPOOL ON
SET NULL 'NO ROWS SELECTED'
set tab off
-- SET HEADING ON
-- SET PAGESIZE 1000
-- SET WRAP OFF
SET LINESIZE 1000
SET FEEDBACK OFF
SET NEWPAGE NONE
SET TRIMS OFF
SET NUMWIDTH 50
-- SET TIMING ON
SET AUTOCOMMIT OFF
SPOOL DB_Report_Tablespace.html

SELECT /* + RULE */ df.tablespace_name "Tablespace",df.bytes / (1024 *
1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

SET MARKUP HTML OFF
SPOOL OFF
exit;

Oracle markup html example output of generated file DB_Report_Tablespace.html :

Tips:

If you want to add some custom header for individual outputs then you can add below sql line to start of any other sql outputs:

select (NULL||' ') "Tablespace Details:" from dual;

Incase you want to send the generated DB_Report_Tablespace.html inline via email you can use below command to do the same in UNIX environments.

echo "`cat DB_Report_Tablespace.html`" | mail -s "$(echo -e "[Database Name] Tablespace Status\nContent-Type: text/html")" to(at)address.com -c cc(at)address.com,cc1(at)address.com -- -f from(at)address.com

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.