Build perfect Excel or HTML output with Sql*plus

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 out feedback off pagesize 999

Set markup HTML on ENTMAP on spool in 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 to 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 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: