Web Reports from SQL *Plus in Oracle 8i/9i

March 7, 2003

by Ajay Gursahani

The SQL *Plus command-line interface enables you to generate a complete HTML output which can be embedded in a web page.

SQL *Plus provides you with a command, SET MARKUP HTML ON SPOOL ON, which is used to produce HTML pages automatically. You can view these pages using any web browser.

The SET MARKUP HTML ON SPOOL ON only specifies that the SQL *Plus output will be HTML encoded; it does not create or begin writing to an output file till you issue SPOOL <filename>. The file will then have HTML tags including <HTML> and </HTML>

You have to use SPOOL OFF to close the spool file and issue SET MARKUP HTML OFF to disable HTML output.

Example:

SQL> desc test

Name                                      Null?    	Type
----------------------------------- 	-------- 	-----------------
UNIQUE_ID                                          	NUMBER
NAME                                               	VARCHAR2(25)
SALARY                                             	NUMBER


SQL> SELECT * FROM TEST;

 UNIQUE_ID NAME                          SALARY
---------- ------------------------- ----------
         1 ANDY                            4500
         2 ALAN                            3500
         3 JACK                            3600
         4 PETER                           4000
         5 JOE                             2900

Issue the following commands at SQL Prompt

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL c:\test.html
SELECT * FROM test;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO OFF

The above commands will generate an HTML file "test.html" which you can view using a web browser. A sample output is as below:

SQL> SELECT * FROM test;

UNIQUE_ID

NAME

SALARY

1

ANDY

4500

2

ALAN

3500

3

JACK

3600

4

PETER

4000

5

JOE

2900

SQL> spool off

Please note that, the SQL query (SELECT * FROM test;) and SPOOL OFF are also a part of "test.html".








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers