HTML output from SQL *PLUS

Normally when we spool the result from SQL*Plus, it creates the text file. We can also create html output using SQL*Plus. Following is the small script to spool the output in html file. File will be created in default location (i.e. $ORACLE_HOME/bin) directory unless specific path is given. Output contains the list of tables along with some other related data.

SET PAGESIZE 50 LINESIZE 120 VERIFY OFF ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK OFF
SET MARKUP HTML ON
SPOOL table_list.html
PROMPT
PROMPT
PROMPT Table List Report
PROMPT =================
COLUMN table_name HEADING ‘Table|Name’ FORMAT a30
COLUMN tablespace_name HEADING ‘Tablespace|Name’ FORMAT a30
COLUMN row_count HEADING ‘Total|Rows’ FORMAT 99G999G999
COLUMN avg_row_len HEADING ‘Average|Row Length’ FORMAT 9999999999
COLUMN last_analyzed HEADING ‘Analyze|Date’ FORMAT a20
COLUMN monitoring HEADING ‘Table|Monitored’ FORMAT a9
SELECT table_name, tablespace_name,num_rows,avg_row_len,last_analyzed,
monitoring
FROM user_tables
ORDER BY TABLE_NAME ASC
/
CLEAR COLUMNS
SPOOL OFF
SET MARKUP HTML OFF

You can save above file as table_list.sql in your local folder or you can cut and paste it straight into SQL*Plus. Let us connect to SQL*Plus and execute the script. Upon successful execution, it will create table_list.html file in $ORACLE_HOME/bin folder.

SQL>Connect scott/tiger@orcl
SQL>@c:\table_list.sql

Following is the output from table_list.html.

Table List Report
=================

Table Name	Tablespace Name	NUM_ROWS	Average Row Length	Analyze Date	Table Monitored
BONUS	USERS	0	0	18-JAN-07	YES
DEPT	USERS	4	20	18-JAN-07	YES
EMP	USERS	14	37	18-JAN-07	YES
SALGRADE	USERS	5	10	18-JAN-07	YES
TEST	USERS	5	16	21-FEB-07	YES

 

If you don’t want SQL*plus screen to jumble up, then use ‘SET TERMOUT OFF’ to suppress the display on the screen. Make sure to turn it on once query is done otherwise you may wonder, why results are not displaying on the screen.