Systems Engineering and RDBMS

HTML output from SQL *PLUS

Posted by decipherinfosys on April 18, 2007

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.

 

Sorry, the comment form is closed at this time.

 
%d bloggers like this: