Use SQL * PLUS to build perfect excel or html output

From: http://hong9270503.blog.163.com/blog/static/127292320091611319516/

 

With SQL * PLUS, we can build friendly output to meet diversified user needs.

　　
In this example, we use SQL * plus to output xls and html files.

First, create two scripts:
　　
1. main. SQL
Used to set the environment and call specific functional scripts

2. function script-get_tables. SQL
　　
Scripts for specific functions

These two scripts can avoid spool redundancy information.

Example:
　　
1. main. SQL script:

[Oracle @ jumper utl_file] $ more main. SQL
Set
Linesize 200
Set term off verify off feedback off pagesize 999
Set
Markup html on entmap ON spool on preformat off
Spool tables.xls
@ Get_tables. SQL
Spool
Off
Exit

2. get_tables. SQL script:

[Oracle @ jumper
Utl_file] $ more get_tables. SQL
Select
Owner, table_name, tablespace_name, blocks, last_analyzed
From all_tables
Order by 1, 2;

3. Execute and obtain the output:

[Oracle @ jumper utl_file] $
Sqlplus "/as sysdba" @ main

SQL * Plus: Release 9.2.0.4.0-
Production on Mon Apr 25 10:30:11 2005

Copyright (c) 1982,200 2,
Oracle Corporation. All rights reserved.

Connected:
Oracle9i
Enterprise Edition Release 9.2.0.4.0-Production
With
Partitioning option
JServer Release 9.2.0.4.0-Production

Disconnected
From Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With
The Partitioning option
JServer Release 9.2.0.4.0-Production
[Oracle @ jumper
Utl_file] $ ls-l tables.xls
-Rw-r -- 1 oracle DBAs 69539 Apr 25
10: 30 tables.xls
[Oracle @ jumper utl_file] $

The output is an xls file.
We can see the output results:

In the main. SQL script, change spool tables.xls to spool.
Tables.htm, we can get the output in htm format, the effect is as follows: