Building Excel or HTML output with Sql*plus

This example introduces the output of xls,html two format files through Sql*plus through a simple example.

Through Sql*plus we can build friendly output to meet the needs of diverse users. This example introduces the output of xls,html two format files through Sql*plus through a simple example.

First create two scripts:

1.main.sql

Used to set up the environment and invoke specific feature scripts.

2. Function Script-get_tables.sql

Scripts to implement specific functions.

With such two scripts, redundant information in spool can be avoided.

Examples are as follows:

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 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, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the 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--r-- 1 oracle dba 69539 Apr 25 10:30 tables.xls
[oracle@jumper utl_file]$

Here the output is XLS file, we can see the output effect by the following figure:

To change the spool Tables.xls in the Main.sql script to spool tables.htm, we can obtain the HTM format output, the effect is as follows: